postgre如何确保sql结果是连续的

Question

我有这样的表：

id   | low_number | high_number
-------------------------------
1    |  12        | 32
-------------------------------
2    |  13        | 33
-------------------------------
3    |   15       | 36
-------------------------------
4    |   33       |  50 
-------------------------------
5    |   35       | 52
...
-------------------------------
17   |   52       |  80

我想得到这样的结果：

id   | low_number | high_number
-------------------------------
1    |  12        | 32
-------------------------------
4    |   33       |  50 
-------------------------------
17   |   52       |   80

这是因为low_number大于前一行high_number。

如何编写sql来获得这些结果？我使用postgresql

Answer 1

这似乎是一个递归的CTE问题。您希望选择第一行(按id(，然后在此基础上选择下一行。

这个想法是一次一行地在行中循环。然后，当满足条件时，转换到该行。等等。

作为一个查询，这看起来像：

with recursive tt as (
select id, low_number, high_number, row_number() over (order by id) as seqnum
from t
),
cte as (
select id, low_number, high_number, seqnum, true as is_change, id as grouping_id
from tt
where seqnum = 1
union all
select tt.id, tt.low_number, tt.high_number, tt.seqnum, tt.low_number > t.high_number,
(case when tt.low_number > t.high_number then tt.id else cte.grouping_id end)
from cte join
t
on cte.grouping_id = t.id join
tt
on tt.seqnum = cte.seqnum + 1
)
select *
from cte
where is_change;

这里有一个db<gt；不停摆弄

Answer 2

使用窗口函数LAG()获取前一行的值，例如

WITH j AS (
SELECT 
id,low_number,high_number, 
LAG(high_number) OVER (ORDER BY id) AS prev_high_number
FROM t)
SELECT id,low_number,high_number  FROM j
WHERE low_number > prev_high_number OR prev_high_number IS NULL;

演示：db<>fiddle