现在有这么一个需求，需要合并序号连续且内容相同的记录范围，原始数据如下所示：
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语句能否实现？
------解决思路----------------------
必须能啊，楼主可以查查孤岛问题，类似你这种情况的
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范围