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

› 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

This topic created in 2456 days ago, the information mentioned may be changed or developed.

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

表结构如下：

table: articles
id   title   top_at    created_at

普通查询

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

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

Supplement 1 · Jan 15, 2020

有没有用程序代码配合，写出更容易维护的代码？

Supplement 2 · Jan 15, 2020

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 replies • 2020-01-16 00:04:20 +08:00

avenger

Jan 14, 2020 via iPhone

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

XiaoxiaoPu

Jan 14, 2020

凭感觉写的，不一定对

上一篇

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

Jan 14, 2020

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

jugelizi

Jan 14, 2020 via iPhone

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

Livid

MOD

PRO

Jan 14, 2020

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

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

DavidNineRoc

Jan 14, 2020

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

RickyC

Jan 14, 2020

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

zhaopengme

Jan 14, 2020 via Android

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

alcarl

Jan 14, 2020

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

Jan 14, 2020 via Android

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

DavidNineRoc

Jan 15, 2020

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

Jochen

Jan 15, 2020

找上一篇 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

Jan 15, 2020

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

Jochen

Jan 15, 2020

@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

Jan 16, 2020 via Android

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