我有一个简单的名为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
For 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
For id 1:
id (current) | hops (current) | id (prev) | hops (prev) | id (next) | hops (next)
1 | 3 | 3 | 5 | 4 | 2
For 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
谢谢
尝试:
select cp.*, n.id id_next, n.hops hops_next
from
(select c.id id_current, c.hops hops_current, p.id id_previous, p.hops hops_previous
from
(select * from users where id = ?) c
left join users p on c.hops < p.hops or (c.id < p.id and c.hops = p.hops)
order by p.hops, p.id limit 1) cp
left join users n
on cp.hops_current > n.hops or (cp.id_current > n.id and cp.hops_current = n.hops)
order by n.hops desc, n.id desc limit 1
(SQLFiddle)
这是迄今为止我见过的最奇怪的用户表。无论如何,这里有一种方法(尽管我不得不承认它有点复杂)…
DROP TABLE IF EXISTS test;
CREATE TABLE test(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,hops INT NOT NULL);
INSERT INTO test VALUES
(1 ,3),(2 ,1),(3,5),(4 ,2),(5 ,6),(6 ,5);
SELECT c.id id_curr
, c.hops hops_curr
, p.id id_prev
, p.hops hops_prev
, n.id id_next
, n.hops hops_next
FROM
(
SELECT a.*
, COUNT(*) new_rank
FROM
( SELECT x.*
, COUNT(*) rank
FROM test x
JOIN test y
ON y.hops >= x.hops
GROUP
BY x.id
) a
JOIN
( SELECT x.*
, COUNT(*) rank
FROM test x
JOIN test y
ON y.hops >= x.hops
GROUP
BY x.id
) b
ON b.rank < a.rank
OR (b.rank = a.rank AND b.id >= a.id)
GROUP
BY a.id
)c
LEFT
JOIN
(
SELECT a.*
, COUNT(*) new_rank
FROM
( SELECT x.*
, COUNT(*) rank
FROM test x
JOIN test y
ON y.hops >= x.hops
GROUP
BY x.id
) a
JOIN
( SELECT x.*
, COUNT(*) rank
FROM test x
JOIN test y
ON y.hops >= x.hops
GROUP
BY x.id
) b
ON b.rank < a.rank
OR (b.rank = a.rank AND b.id >= a.id)
GROUP
BY a.id
) p
ON p.new_rank = c.new_rank-1
LEFT
JOIN
(
SELECT a.*
, COUNT(*) new_rank
FROM
( SELECT x.*
, COUNT(*) rank
FROM test x
JOIN test y
ON y.hops >= x.hops
GROUP
BY x.id
) a
JOIN
( SELECT x.*
, COUNT(*) rank
FROM test x
JOIN test y
ON y.hops >= x.hops
GROUP
BY x.id
) b
ON b.rank < a.rank
OR (b.rank = a.rank AND b.id >= a.id)
GROUP
BY a.id
) n
ON n.new_rank = c.new_rank+1
ORDER
BY c.hops DESC
, c.id DESC;