我有一个名为users的简单表,其中包含以下数据:
id | hops
1 | 3
2 | 1
3 | 5
4 | 2
5 | 6
6 | 5
我想根据跳的降序进行上一个/下一个导航.我使用以下查询来对降序进行排序:
SELECT * FROM users ORDER BY hops DESC, id DESC
结果如下:
id | hops
5 | 6
6 | 5
3 | 5
1 | 3
4 | 2
2 | 1
现在我想要的是,当我在mysql查询中输入任何ID时,根据上述排序,我得到了上一个和下一个ID.例如:
对于id 5(在这种情况下,id = 5的跳数最高,因此之前没有任何记录):
id (current) | hops (current) | id (prev) | hops (prev) | id (next) | hops (next)
5 | 6 | NULL | NULL | 6 | 5
对于ID 6:
id (current) | hops (current) | id (prev) | hops (prev) | id (next) | hops (next)
6 | 5 | 5 | 6 | 3 | 5
对于ID 3:
id (current) | hops (current) | id (prev) | hops (prev) | id (next) | hops (next)
3 | 5 | 6 | 5 | 1 | 3
对于ID 1:
id (current) | hops (current) | id (prev) | hops (prev) | id (next) | hops (next)
1 | 3 | 3 | 5 | 4 | 2
对于ID 4:
id (current) | hops (current) | id (prev) | hops (prev) | id (next) | hops (next)
4 | 2 | 1 | 3 | 2 | 1
对于id 2(在这种情况下,id = 2的跳数最低,因此之后没有后续记录)
id (current) | hops (current) | id (prev) | hops (prev) | id (next) | hops (next)
2 | 1 | 4 | 2 | NULL | NULL
谢谢