mysql - 根据按两个文本列排序的 SQL 获取下一行和上一行

Question

我有这个sql语句:

SELECT * FROM members ORDER BY last_name ASC, first_name ASC LIMIT 0, 10

以上是在索引页上使用的。

在详情页面，我使用

SELECT * FROM members WHERE id = :id

如何根据当前行id获取上一行和下一行？ (上一个和下一个按last_name ASC、first_name ASC 排序)

我认为它会是这样的:

SELECT id FROM members WHERE last_name >= : last_name AND id != :id ORDER BY last_name ASC, first_name ASC

但这是不对的。我还需要以某种方式将first_name添加到语句中，并且仅执行first_name>=:first_name是不正确的。

Answer 1

您可以将union all与order by和limit一起使用。以下代码获取第一个子查询中的前一个名称和第二个子查询中的下一个名称:

(select m.*
 from members m
 where last_name < (select last_name from members where id = :id)
 order by last_name desc
 limit 1
) union all
(select m.*
 from members m
 where last_name > (select last_name from members where id = :id)
 order by last_name asc
 limit 1
);

处理名字和姓氏的最简单方法可能是将它们连接在一起:

(select m.*
 from members m
 where concat_ws(' ', last_name, first_name) < (select concat_ws(' ', last_name, first_name) from members where id = :id)
 order by last_name asc, first_name desc
 limit 1
) union all
(select m.*
 from members m
 where concat_ws(' ', last_name, first_name) > (select concat_ws(' ', last_name, first_name) from members where id = :id)
 order by last_name asc, first_name
 limit 1
)

如果名字和/或姓氏中有空格，这可能会产生一些意想不到的结果。我猜数据中不会发生这种情况，但在这种情况下可以使用其他分隔符。