如何选择任意行的相邻行(在SQL或PostgreSQL中)?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (68)

我想要根据某些标准选择一些行,然后从该集合中选择一个条目,然后从它之前和之后的5行中选择一个条目。

现在,如果表上有主键(例如,主键比目标行的键少5个,比目标行的键多5个),我可以在数字上这样做。

因此,选择主键为7的行和附近的行:

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

但是,如果我一开始只选择了某些行,我就失去了使用主键的数字方法(这是假设键在顺序上没有任何空隙),并且需要另一种方法来在某个目标行之前和之后获得最近的行。

这样的选择的主键输出看起来可能更随机,因此对数学定位不太容易执行(因为一些结果会被过滤,例如,使用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

请注意,由于主键中的空白是如何由其中的条件引起的(例如,因为有许多不活动的项目。),我不再得到最接近的上面5和下面5,相反,我得到的是下面最近的1和上面最近的9。

提问于
用户回答回答于

SQL查询中有一种方法:

(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行。

用户回答回答于

您需要使用子查询或CTE的。这里有一个示例,它将与pagila示例数据库一起工作。

WITH base AS (
    SELECT lag(customer_id, 5) OVER (ORDER BY customer_id) lag, 
      lead(customer_id, 5) OVER (ORDER BY customer_id) lead, 
      c.*
    FROM customer c
    WHERE c.active = 1
    AND c.last_name LIKE 'B%'
) 
SELECT base.* FROM base 
JOIN (
  -- Select the center row, coalesce so it still works if there aren't 
  -- 5 rows in front or behind
  SELECT COALESCE(lag, 0) AS lag, COALESCE(lead, 99999) AS lead 
  FROM base WHERE customer_id = 280
) sub ON base.customer_id BETWEEN sub.lag AND sub.lead

扫码关注云+社区