V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
DavidNineRoc
V2EX  ›  MySQL

MySQL 两个字段排序,怎么查找出上一篇下一篇?

  •  
  •   DavidNineRoc · 2020-01-14 18:39:02 +08:00 · 4679 次点击
    这是一个创建于 1535 天前的主题,其中的信息可能已经有所发展或是发生改变。

    不用存储过程的前提下,有没有什么办法获取?


    表结构如下:

    table: articles
    id   title   top_at    created_at
    

    普通查询

    select * from articles order by top_at desc, created_at desc;
    

    以上是在列表页查询,如果我查看了某个文章,然后想知道上一篇下一篇应该怎么样才能得知 id 呢?

    第 1 条附言  ·  2020-01-15 10:10:12 +08:00
    有没有用程序代码配合,写出更容易维护的代码?
    第 2 条附言  ·  2020-01-15 13:34:45 +08:00
    create table articles(
    
    	id int(12) not null auto_increment primary key,
    	title varchar(255),
    	top_at datetime,
    	created_at datetime
    );
    
    
    insert into articles(title, created_at, top_at) values
    ('第一篇', '2020-01-01 09:48:45', '2020-01-12 10:48:53'),
    ('第二篇', '2020-01-12 09:55:46', null),
    ('第三篇', '2020-01-10 17:55:46', null);
    
    select * from articles order by top_at desc, created_at desc;
    
    
    
    SET @id = 2;
    SELECT
    * 
    FROM
    articles 
    WHERE
    top_at > ( SELECT top_at FROM articles WHERE id = @id ) 
    OR (top_at = ( SELECT top_at FROM articles WHERE id = @id ) AND created_at >= (SELECT created_at FROM articles WHERE id = @id))
    AND id != @id 
    ORDER BY top_at ASC, created_at ASC
    LIMIT 1;
    
    15 条回复    2020-01-16 00:04:20 +08:00
    avenger
        1
    avenger  
       2020-01-14 18:46:26 +08:00 via iPhone
    有主键的话 where id > currentId limit 1 ?
    XiaoxiaoPu
        2
    XiaoxiaoPu  
       2020-01-14 18:51:08 +08:00
    凭感觉写的,不一定对


    上一篇

    select id
    from articles
    where
    top_at >= (select top_at where id = 123456)
    and created_at >= (select created_at where id = 123456)
    and id != 123456
    order by top_at asc, created_at asc
    limit 1;

    下一篇
    select id
    from articles
    where
    top_at <= (select top_at where id = 123456)
    and created_at <= (select created_at where id = 123456)
    and id != 123456
    order by top_at desc, created_at desc
    limit 1;
    DavidNineRoc
        3
    DavidNineRoc  
    OP
       2020-01-14 19:03:45 +08:00
    @avenger 数据并不是按照 id 排序。
    @XiaoxiaoPu 两条 SQL 都试了,查出空集无数据。
    jugelizi
        4
    jugelizi  
       2020-01-14 19:06:46 +08:00 via iPhone
    按照你定义的排序规则的字段的值去取呗
    Livid
        5
    Livid  
    MOD
       2020-01-14 19:07:19 +08:00
    列表页链接到内容页的时候,传 loop index,然后内容页可以用 limit 找到前后。

    https://www.guru99.com/limit.html
    DavidNineRoc
        6
    DavidNineRoc  
    OP
       2020-01-14 19:47:19 +08:00
    @jugelizi 现在两个字段排序,单字段可以。
    @Livid 实际上就通过 offset 维护,这个有想过。但是有一个问题就是如果是在搜索引擎引流过来的,直接到详情页面。无法得到 offset。
    RickyC
        7
    RickyC  
       2020-01-14 20:00:48 +08:00
    select * from articles order by top_at desc, created_at desc limit 页码,1
    ----
    如果不用 id 找上一篇和下一篇呢?
    zhaopengme
        8
    zhaopengme  
       2020-01-14 21:10:02 +08:00 via Android
    刚写过 核心思路 order max/min limit 1
    alcarl
        9
    alcarl  
       2020-01-14 23:19:32 +08:00
    select a.*,b.id as nextId from (
    select * from (
    SELECT id, @aRank := @aRank + 1 AS rank
    FROM articles p, (
    SELECT @aRank := 0
    ) q
    order by top_at desc, created_at desc
    ) a
    where id =2048
    ) as a
    left join
    (
    SELECT id, @bRank := @bRank + 1 AS rank
    FROM articles p, (
    SELECT @bRank := 0
    ) q
    order by top_at desc, created_at desc
    ) as b
    on a.rank=b.rank-1

    。。。。。。这种写法应该可以满足需要,但性能是在没有办法,如果表条数过多,两个子查询的遍历会很慢,排序列有索引且包含 id 列应该能快点,也就这意思了。最好还是维护一个序号表来解决这个需求。
    用上 mysql8.0 cte 和 rank 函数可能能快一点,我没有环境也没有办法测试。。。。。
    matrix1010
        10
    matrix1010  
       2020-01-14 23:57:46 +08:00 via Android
    什么版本的 mysql? 8 的 windows function 里有 lead 和 lag
    DavidNineRoc
        11
    DavidNineRoc  
    OP
       2020-01-15 10:09:21 +08:00
    @RickyC 页码哪来?推文章我要更新了排序。同样的 URL 却不是同一篇文章。
    @zhaopengme 看一下题,两个排序字段。单字段你的可以解决
    @alcarl 这还是 nextId,再来个 lastId
    @matrix1010 5.7, 如果高版本能解决,我打算升版本
    Jochen
        12
    Jochen  
       2020-01-15 10:18:50 +08:00
    找上一篇 SQL:
    SET @id = 12345;
    SELECT
    *
    FROM
    article
    WHERE
    top_at > ( SELECT top_at FROM article WHERE id = @id )
    OR (top_at = ( SELECT top_at FROM article WHERE id = @id ) AND created_at >= (SELECT created_at FROM article WHERE id = @id))
    AND id != @id
    ORDER BY top_at ASC, created_at ASC
    LIMIT 1;

    我在本地模拟了几条 SQL 是 OK 的。
    DavidNineRoc
        13
    DavidNineRoc  
    OP
       2020-01-15 13:35:10 +08:00
    @Jochen 不行,请看一下 append, 查询出来的结果为空
    Jochen
        14
    Jochen  
       2020-01-15 15:37:17 +08:00
    @DavidNineRoc 查询结果为空是因为 top_at 字段的值可以为 null,而在 MySQL 中,NULL 值与任何其它值的比较(即使是 NULL )永远返回 NULL,即 NULL = NULL 返回 NULL。

    针对这种情况,可以稍微处理一下:
    SET @id = 12345;
    SELECT
    *
    FROM
    articles
    WHERE
    IFNULL(top_at,1) > ( SELECT IFNULL(top_at,1) FROM articles WHERE id = @id )
    OR (IFNULL(top_at,1) = ( SELECT IFNULL(top_at,1) FROM articles WHERE id = @id ) AND created_at >= (SELECT
    created_at FROM articles WHERE id = @id))
    AND id != @id
    ORDER BY top_at ASC, created_at ASC
    LIMIT 1;
    alcarl
        15
    alcarl  
       2020-01-16 00:04:20 +08:00 via Android
    @DavidNineRoc 我的哥。。。。。最后一行改成 b.rank+1
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   3270 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 28ms · UTC 14:03 · PVG 22:03 · LAX 07:03 · JFK 10:03
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.