代码之家 › 专栏 › 技术社区 › Kzqai

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

selection postgresql sql

Kzqai · 技术社区 · 14 年前

我想根据特定的条件选择一些行,然后从该集合中选择一个条目以及它前后的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

注意,由于主键中的间隙所导致的示例where条件( ),我不再得到上面最接近的5和下面最接近的5,而是得到下面最接近的1和上面最接近的9。

5 回复 | 直到 10 年前

jchamberlain 10 年前

如果用一种编程语言运行两个查询,有很多方法可以做到这一点,但在一个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

Scott Bailey 14 年前

这里有另一种方法来处理分析函数lead和lag。如果我们能在WHERE子句中使用解析函数就好了。因此,您需要使用子查询或CTE。

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

razon 10 年前

对于类似的查询,我使用不带CTE的分析函数。比如:

select ..., LEAD(gm.id) OVER (ORDER BY Cit DESC) as leadId, LEAD(gm.id, 2) OVER (ORDER BY Cit DESC) as leadId2, LAG(gm.id) OVER (ORDER BY Cit DESC) as lagId, LAG(gm.id, 2) OVER (ORDER BY Cit DESC) as lagId2 ... where id = 25912 or leadId = 25912 or leadId2 = 25912 or lagId = 25912 or lagId2 = 25912

这样的查询对我来说比使用join的CTE更快(Scott Bailey的回答)。但当然不那么优雅

sgriffinusa 14 年前

您可以使用row\u number()来实现这一点(从8.4开始提供)。这可能不是正确的语法(不熟悉postgresql),但希望能说明这个想法:

SELECT *
FROM (SELECT ROW_NUMBER() OVER (ORDER BY primary_key) AS r, *
      FROM table
      WHERE active=1) t
WHERE 25 < r and r < 35

这将生成具有序列号的第一列。您可以使用它来标识单行及其上方和下方的行。

Tom Anderson 14 年前

如果您想以“纯关系”的方式进行,可以编写一个对行进行排序和编号的查询。比如:

select (
  select count(*) from employees b
  where b.name < a.name
) as idx, name
from employees a
order by name

然后将其用作公共表表达式。写一个select,将它过滤到您感兴趣的行,然后使用一个条件将它连接回自身,即表的右侧副本的索引不大于k或小于左侧行的索引。投射到右边的行上。比如:

with numbered_emps as (
  select (
    select count(*)
    from employees b
    where b.name < a.name
  ) as idx, name
  from employees a
  order by name
)
select b.*
from numbered_emps a, numbered_emps b
where a.name like '% Smith' -- this is your main selection criterion
and ((b.idx - a.idx) between -5 and 5) -- this is your adjacency fuzzy-join criterion

再简单不过了!