通过行号查询上一篇下一篇文章,可以很好的解决多条件、多字段排序造成无法准确获取上一篇下一篇文章的问题。
# 列表查询
SELECT * FROM basic_city WHERE parent_id=70 ORDER BY parent_id,first_letter;
以下代行号的列表只做演示用,正常场景下还是以上面这个为准:
# 代行号的列表查询
SELECT @r:=@r+1 AS row_no,c.*
FROM basic_city AS c,(SELECT @r:=0) AS r
WHERE parent_id=70
ORDER BY parent_id,first_letter;
一、先获取指定id等于767的数据在列表里所在的行号(row_no):
SELECT row_no FROM (
SELECT @r:=@r+1 AS row_no,c.*
FROM basic_city AS c,(SELECT @r:=0) AS r
WHERE parent_id=70
ORDER BY parent_id,first_letter
) AS tmp WHERE id=767;
二、通过行号获取上一篇文章:
SELECT * FROM (
SELECT @r:=@r+1 AS row_no,c.*
FROM basic_city AS c,(SELECT @r:=0) AS r
WHERE parent_id=70
ORDER BY parent_id,first_letter
) AS tmpWHERE row_no<4
ORDER BY row_no DESC
LIMIT 1;
三、通过行号获取下一篇文章:
SELECT * FROM (
SELECT @r:=@r+1 AS row_no,c.*
FROM basic_city AS c,(SELECT @r:=0) AS r
WHERE parent_id=70
ORDER BY parent_id,first_letter
) AS tmpWHERE row_no>4
ORDER BY row_no ASC
LIMIT 1;
注:SELECT @r:=0 、@r:=@r+1 这两条语句的原理还不太理解,只知道它是用来生成行号的,有知道的小伙伴望指教,不胜感谢!!!