如何在SQL中按连续记录分组 [英] How to group by continuous records in SQL

问题描述

我的表中有这些记录

ID  Colour
------------
 1   Red
 2   Red
 3   Red
 4   Red
 5   Red
 6   Green
 7   Green
 8   Green
 9   Green
10   Red
11   Red
12   Red
13   Red
14   Green
15   Green
16   Green
17   Blue
18   Blue
19   Red
20   Blue

我可以像这样轻松地按颜色分组

SELECT Colour, MIN(ID) AS iMin, MAX(ID) AS iMax
FROM MyTable
GROUP BY Colour

这将返回此结果

Colour     iMin     iMax
-------------------------
Red        1        19
Green      6        16
Blue       17       20

但这不是我想要的，因为红色不会一直从1到19，绿色打破了顺序。

结果应该是这样的

Colour     iMin     iMax
------------------------
Red        1        5
Green      6        9
Red        10       13
Green      14       16
Blue       17       18
Red        19       19
Blue       20       20

我设法通过游标做到了这一点，但我想知道有没有更有效的方法

推荐答案

这是一个缺口和孤岛问题。假设id不断递增，您可以使用row_number()之间的差异来定义具有相同colour的"相邻"记录组：

select 
    colour, 
    min(id) iMin,
    max(id) iMax
from (
    select t.*, row_number() over(partition by colour order by id) rn
    from mytable t
) t
group by colour, id - rn
order by min(id)

Demo on DB Fiddle：

colour | iMin | iMax
:----- | ---: | ---:
Red    |    1 |    5
Green  |    6 |    9
Red    |   10 |   13
Green  |   14 |   16
Blue   |   17 |   18
Red    |   19 |   19
Blue   |   20 |   20

这篇关于如何在SQL中按连续记录分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！