连续区间（数据岛）查询

数据源如下

CREATE TABLE [dbo].[T1](
	[col1] [int] NOT NULL,
 CONSTRAINT [PK_T1] PRIMARY KEY CLUSTERED 
(
	[col1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

　　T1中col1的序列号并不连续，我们需要找到现有值的连续区间，

方案1：使用子查询解决

select MIN(col1) as start_range,Max(col1) as end_range from
(select col1,(select MIN(B.col1) from T1 as B where B.col1>=A.col1 and not exists (select * from T1 as C where C.col1=B.col1+1)) as grp
from T1 as A) as D group by grp

方案2：使用窗口函数解决

select MIN(col1) as start_range,MAX(col1) as end_range from
(select col1, col1-ROW_NUMBER() over(order by col1) as grp from T1) as A
group by grp

 

转载于:https://www.cnblogs.com/xiaopotian/p/6805426.html