在当前商品ID周围选择N个上一个商品和M个下一个商品 [英] Select N previous and M next items around current item id

问题描述

我有一张带照片的桌子

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的照片.例如这样的

I select photo with a given id, somewhere in the middle of all photos. For example like this:

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

这会给我:

5,3,7,6,4

这给了我所有照片的清单.现在，我将此列表写在我的网络上，但用户可以单击某张照片.之后，将打开详细页面.但是从这个详细的页面，我希望能够转到下一个" M和上一个" N张照片.这意味着，我需要根据当前选择的ID来选择相邻的ID.该怎么办?

This gives me list of all photos. Now, I write this list on my web, but user can click on one certain photo. After that, detailed page opens. But from this detailed page, I would like to be able to go to "next" M and "previous" N photos. This means, I need to select neihboring IDs based on currenttly selected one. How can this be done?

现在，我选择id = 7，我希望邻居成为: prev: 5,3和next: 6,4.如何选择呢?

Now I select id = 7 and I want neighbors to be: prev: 5,3 and next: 6,4. How can this be selected?

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

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

我无法在PHP中运行相同的查询并过滤结果，因为查询可能包含LIMITS(例如，使用LIMIT 2, 4我仍然需要正确的邻居)

I cannot run the same query and filter results in PHP, because query can contain LIMITS (eg. with LIMIT 2, 4 I still the need correct neighbors)

推荐答案

一旦选定ID为7的行的year和comm_count值，您就可以进行两个简单的查询:

Once you have the year and comm_count values for the selected row with id=7, you can make two simple queries:

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，则可以使用

If you use MySQL 8.0, you can use the LAG() and LEAD() functions.

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 |
+----+------+------+-----------+------+-----------+

这篇关于在当前商品ID周围选择N个上一个商品和M个下一个商品的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！