SQL 连续范围

[0808xyj]

现在有这么一个需求，需要合并序号连续且内容相同的记录范围，原始数据如下所示：
Id status
1 good
2 bad
3 good
4 good
5 good
6 bad
7 bad

输出的效果如下：

id范围 status 相同的个数
1 good 1
2 bad 1
3-5 good 3
6-7 bad 2

要达到这种效果，一条SQL语句能否实现？

[专注or全面]

必须能啊，楼主可以查查孤岛问题，类似你这种情况的

create table test_1(number int,status varchar(10))
insert into test_1 values 
(1,'A'),(2,'B'),(3,'A'),(4,'A'),(5,'A'),(6,'B'),(7,'B')

with cte as
(
	select ROW_NUMBER()over(partition by status order by number) as ID,cast(number as varchar(10)) as number,status from test_1
)
select distinct
	case when( MIN(number)over(partition by flag,status)=MAX(number)over(partition by flag,status)) 
		then MAX(number)over(partition by flag,status)
	else
	 MIN(number)over(partition by flag,status) +'~'+MAX(number)over(partition by flag,status)
	end 
	 as result,
         status,
	 CAST(MAX(number)over(partition by flag,status) AS INT) - CAST(MIN(number)over(partition by flag,status) AS INT)+1 AS CNT
	from
	(
		select id  ,number,number-id as flag,status from cte
	) t 
	ORDER BY result


result                status     CNT
--------------------- ---------- -----------
1                     A          1
2                     B          1
3~5                   A          3
6~7                   B          2

(4 行受影响)

[习惯性蹭分]

create table T(id int,[status] varchar(30))
insert into T
select 1, 'good' union all
select 2, 'bad'  union all
select 3, 'good' union all
select 4, 'good' union all
select 5, 'good' union all
select 6, 'bad'  union all
select 7, 'bad'  

;with sel as
(select id,[status],row_number() over(partition by [status] order by id) as rn from T
) 
,sel2 as( 
select min(id) as minID,max(id) as maxID,[status],count(id) as 相同个数
from sel 
group by rn-id,[status]
) select case when minID=maxID then ltrim(minID) else ltrim(minID)+'-'+ltrim(maxID) end as ID范围,
status,相同个数
 from sel2
 order by ID范围