时间戳

首页 > 解决方案 > 在当前项目 id 周围选择 N 个上一个和 M 个下一个项目

mysql - 在当前项目 id 周围选择 N 个上一个和 M 个下一个项目

问题描述

我有一张有照片的桌子

id | year| comm_count
 0   2015         1
 1   2016         2
 2   2017         5
 3   2018         7
 4   2019         1
 5   2020         9
 6   2021         1
 7   2022         1

我选择具有给定 ID 的照片，位于所有照片中间的某个位置。例如像这样：

SELECT * 
FROM photo 
WHERE year > '2017' 
ORDER BY comm_count DESC, year DESC

这会给我：

5,3,7,6,4

这给了我所有照片的列表。现在，我在我的网络上写了这个列表，但用户可以点击一张特定的照片。之后，详细页面打开。但是从这个详细的页面，我希望能够转到“下一张”M 和“上一张”N 张照片。这意味着，我需要根据当前选择的 ID 来选择相邻的 ID。如何才能做到这一点？

现在我选择id = 7并且我希望邻居是： prev: 5,3和next: 6,4。这个怎么选？

SqlFiddle - http://sqlfiddle.com/#!9/4f3f42/4/0

我不能在 PHP 中运行相同的查询和过滤结果，因为查询可以包含限制（例如，LIMIT 2, 4我仍然需要正确的邻居）

标签： mysql

解决方案

一旦获得了 id=7 的选定行的year和comm_count值，就可以进行两个简单的查询：

SELECT * FROM photo 
WHERE year > 2017 AND (comm_count = 1 AND year <= 2022 OR comm_count < 1) 
ORDER BY comm_count DESC, year DESC LIMIT 3 OFFSET 1
+----+------+------------+
| id | year | comm_count |
+----+------+------------+
|  6 | 2021 |          1 |
|  4 | 2019 |          1 |
+----+------+------------+

SELECT * FROM photo 
WHERE year > 2017 AND (comm_count = 1 AND year >= 2022 OR comm_count > 1) 
ORDER BY comm_count ASC, year ASC LIMIT 3 OFFSET 1;
+----+------+------------+
| id | year | comm_count |
+----+------+------------+
|  3 | 2018 |          7 |
|  5 | 2020 |          9 |
+----+------+------------+

如果您使用 MySQL 8.0，则可以使用LAG() 和 LEAD() 函数。

SELECT id, year, 
  LAG(id, 1) OVER w AS next,
  LAG(id, 2) OVER w AS next_next,
  LEAD(id, 1) OVER w AS prev,
  LEAD(id, 2) OVER w AS prev_prev
FROM photo 
WHERE year > 2017
WINDOW w AS (ORDER BY comm_count DESC, year DESC)

+----+------+------+-----------+------+-----------+
| id | year | next | next_next | prev | prev_prev |
+----+------+------+-----------+------+-----------+
|  5 | 2020 | NULL |      NULL |    3 |         7 |
|  3 | 2018 |    5 |      NULL |    7 |         6 |
|  7 | 2022 |    3 |         5 |    6 |         4 |
|  6 | 2021 |    7 |         3 |    4 |      NULL |
|  4 | 2019 |    6 |         7 | NULL |      NULL |
+----+------+------+-----------+------+-----------+

推荐阅读