mysql 卡在接头上

vq8itlhq 于 2023-03-07 发布在 Mysql

关注(0)|答案(1)|浏览(77)

我喜欢选择按ID排序的所选行之前的2行和之后的2行。

ID:1 - 2 - 3 - 4 - 5 - 6 - 8 - 9 - 10

假设$skip是6，这就是我想要实现的：

4 - 5 - 7 - 8

我的代码：

(SELECT * FROM $table WHERE id < $skip ORDER BY id DESC LIMIT 2) union all (SELECT * FROM $table WHERE id > $skip ORDER BY id ASC LIMIT 2)

这是我得到的：

5 - 4 - 7 - 8

这并没有修复它：

((SELECT * FROM $table WHERE id < $skip ORDER BY id DESC LIMIT 2) ORDER BY id ASC) union all (SELECT * FROM $table WHERE id > $skip ORDER BY id ASC LIMIT 2)

测试这个排序是正确的，所以我想问题一定是联合所有？

SELECT * FROM $table WHERE id < $skip ORDER BY id DESC LIMIT 2

你知道怎么修第一部分吗？

mysql

来源：https://stackoverflow.com/questions/75572869/stuck-with-union

1条答案

按热度按时间

5ktev3wc1#

在子查询中有order by，但也应该在外部查询中添加一个：

(SELECT * FROM $table WHERE id < $skip ORDER BY id DESC LIMIT 2) 
UNION ALL 
(SELECT * FROM $table WHERE id > $skip ORDER BY id ASC LIMIT 2)
ORDER BY id;

或者，您可以使用窗口函数（如果您使用的是MySQL 8+）：

WITH base AS (
  SELECT id, 
         LEAD(id) OVER (ORDER BY id) AS next_id,
         LEAD(id, 2) OVER (ORDER BY id) AS next2_id,
         LAG(id) OVER (ORDER BY id) AS prev_id,
         LAG(id, 2) OVER (ORDER BY id) AS prev2_id
  FROM $table
)
SELECT id FROM base WHERE $skip IN (next_id, next2_id, prev_id, prev2_id)
ORDER BY id;

赞(0）回复(0）举报 2023-03-07

我来回答

mysql 卡在接头上

1条答案

相关问题

热门标签

最新问答