SQL如何查询重新排序后的上一条和下一条记录
表Table有以下几列
主键id自增 title content a_order
1 a aaa 0
2 b bbb 2
3 c ccc 1
4 d ddd 0
5 e eee 2
6 f fff 0
如果用select * from Table order by a_order desc,id desc排序，结果就是
5 e eee 2
2 b bbb 2
3 c ccc 1
6 f fff 0
4 d ddd 0
1 a aaa 0
这个时候，如何读取 id=6的上一条和下一条
[解决办法]
with TB as (
select *,row_number() over(order by a_order desc,id desc) as rowid from Table order by a_order desc,id desc)
--上一条：
select * from TB where rowid=(select rowid-1 from TB where id=6)
--下一条 你懂的
[解决办法]
;with TB as (
select *,row_number() over(order by a_order desc,id desc) as rowid from Table order by a_order desc,id desc)
select * from tb
