一旦你拥有了year
and comm_count
id=7 的所选行的值,您可以进行两个简单的查询:
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() 函数 https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_lag.
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 |
+----+------+------+-----------+------+-----------+