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

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

这是一个创建于 2122 天前的主题，其中的信息可能已经有所发展或是发生改变。

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

表结构如下：

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

2020-01-14 18:46:26 +08:00 via iPhone

有主键的话 where id > currentId limit 1 ？

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

2020-01-14 19:03:45 +08:00

@avenger 数据并不是按照 id 排序。
@XiaoxiaoPu 两条 SQL 都试了，查出空集无数据。

jugelizi

2020-01-14 19:06:46 +08:00 via iPhone

按照你定义的排序规则的字段的值去取呗

Livid

MOD

PRO

2020-01-14 19:07:19 +08:00

列表页链接到内容页的时候，传 loop index，然后内容页可以用 limit 找到前后。

https://www.guru99.com/limit.html

DavidNineRoc

2020-01-14 19:47:19 +08:00

@jugelizi 现在两个字段排序，单字段可以。
@Livid 实际上就通过 offset 维护，这个有想过。但是有一个问题就是如果是在搜索引擎引流过来的，直接到详情页面。无法得到 offset。

RickyC

2020-01-14 20:00:48 +08:00

select * from articles order by top_at desc, created_at desc limit 页码,1
----
如果不用 id 找上一篇和下一篇呢?

zhaopengme

2020-01-14 21:10:02 +08:00 via Android

刚写过核心思路 order max/min limit 1

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

2020-01-14 23:57:46 +08:00 via Android

什么版本的 mysql? 8 的 windows function 里有 lead 和 lag

DavidNineRoc

2020-01-15 10:09:21 +08:00

@RickyC 页码哪来？推文章我要更新了排序。同样的 URL 却不是同一篇文章。
@zhaopengme 看一下题，两个排序字段。单字段你的可以解决
@alcarl 这还是 nextId，再来个 lastId
@matrix1010 5.7，如果高版本能解决，我打算升版本

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

2020-01-15 13:35:10 +08:00

@Jochen 不行，请看一下 append，查询出来的结果为空

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

2020-01-16 00:04:20 +08:00 via Android

@DavidNineRoc 我的哥。。。。。最后一行改成 b.rank+1