不用存储过程的前提下,有没有什么办法获取?
表结构如下:
table: articles
id title top_at created_at
普通查询
select * from articles order by top_at desc, created_at desc;
以上是在列表页查询,如果我查看了某个文章,然后想知道上一篇下一篇应该怎么样才能得知 id 呢?
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;
1
avenger 2020-01-14 18:46:26 +08:00 via iPhone
有主键的话 where id > currentId limit 1 ?
|
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; |
3
DavidNineRoc OP |
4
jugelizi 2020-01-14 19:06:46 +08:00 via iPhone
按照你定义的排序规则的字段的值去取呗
|
5
Livid MOD |
6
DavidNineRoc OP |
7
RickyC 2020-01-14 20:00:48 +08:00
select * from articles order by top_at desc, created_at desc limit 页码,1
---- 如果不用 id 找上一篇和下一篇呢? |
8
zhaopengme 2020-01-14 21:10:02 +08:00 via Android
刚写过 核心思路 order max/min limit 1
|
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 函数可能能快一点,我没有环境也没有办法测试。。。。。 |
10
matrix1010 2020-01-14 23:57:46 +08:00 via Android
什么版本的 mysql? 8 的 windows function 里有 lead 和 lag
|
11
DavidNineRoc OP @RickyC 页码哪来?推文章我要更新了排序。同样的 URL 却不是同一篇文章。
@zhaopengme 看一下题,两个排序字段。单字段你的可以解决 @alcarl 这还是 nextId,再来个 lastId @matrix1010 5.7, 如果高版本能解决,我打算升版本 |
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 的。 |
13
DavidNineRoc OP @Jochen 不行,请看一下 append, 查询出来的结果为空
|
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; |
15
alcarl 2020-01-16 00:04:20 +08:00 via Android
@DavidNineRoc 我的哥。。。。。最后一行改成 b.rank+1
|