如何选择任意行的相邻行(在 sql 或 postgresql 中)? [英] How can I select adjacent rows to an arbitrary row (in sql or postgresql)?

问题描述

我想根据特定条件选择一些行，然后从该集合中选取一个条目及其前后 5 行.

I want to select some rows based on certain criteria, and then take one entry from that set and the 5 rows before it and after it.

现在，如果表上有主键，我可以用数字来执行此操作(例如，主键在数字上比目标行的键少 5，比目标行的键多 5).

Now, I can do this numerically if there is a primary key on the table, (e.g. primary keys that are numerically 5 less than the target row's key and 5 more than the target row's key).

所以选择主键为 7 的行和附近的行:

So select the row with the primary key of 7 and the nearby rows:

select primary_key from table where primary_key > (7-5) order by primary_key limit 11;

2
3
4
5
6
-=7=-
8
9
10
11
12

但是如果我只选择某些行作为开始，我就会失去使用主键的数字方法(并且假设键的顺序没有任何间隙)，并且需要另一种方法来获得最接近的特定目标行之前和之后的行.

But if I select only certain rows to begin with, I lose that numeric method of using primary keys (and that was assuming the keys didn't have any gaps in their order anyway), and need another way to get the closest rows before and after a certain targeted row.

这种选择的主键输出可能看起来更随机，因此不太容易接受数学定位(因为某些结果会被过滤掉，例如使用 where active=1):

The primary key output of such a select might look more random and thus less succeptable to mathematical locating (since some results would be filtered, out, e.g. with a where active=1):

select primary_key from table where primary_key > (34-5) 
    order by primary_key where active=1 limit 11;

30
-=34=-
80
83
100
113
125
126
127
128
129

请注意，由于示例 where 条件(例如因为有许多非活动项目)导致主键中的间隙，我不再获得最接近的 5 以上和 5 以下，而不是我得到最接近的下方 1 和上方最接近的 9.

Note how due to the gaps in the primary keys caused by the example where condition (for example becaseu there are many inactive items), I'm no longer getting the closest 5 above and 5 below, instead I'm getting the closest 1 below and the closest 9 above, instead.

推荐答案

如果您使用一种编程语言运行两个查询，有很多方法可以做到这一点，但这里有一种方法可以在一个 SQL 查询中完成:

There's a lot of ways to do it if you run two queries with a programming language, but here's one way to do it in one SQL query:

(SELECT * FROM table WHERE id >= 34 AND active = 1 ORDER BY id ASC LIMIT 6)
UNION
(SELECT * FROM table WHERE id < 34 AND active = 1 ORDER BY id DESC LIMIT 5)
ORDER BY id ASC

这将返回上面的 5 行、目标行和下面的 5 行.

This would return the 5 rows above, the target row, and 5 rows below.

这篇关于如何选择任意行的相邻行(在 sql 或 postgresql 中)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！