select col,id=identity(int,1,1) into #tb from tbselect count(*) cnt from #tb group by col-id
declare @s varchar(1000);with cte1 as ( select col,row_number() over(order by col) as num from tab a where not exists ( select 1 from tab where col = a.col -1 ) ),cte2 as ( select col,row_number() over(order by col) as num from tab a where not exists ( select 1 from tab where col = a.col +1 ) ) select @s = isnull(@s + ',','') + cast(cte2.col - cte1.col + 1 as varchar(10)) from cte1,cte2 where cte1.num = cte2.num order by cte1.numprint @s
if object_id('tb','u') is not null drop table tb gocreate table tb( num int ) goinsert into tb(num) select 1 union all select 2 union all select 4 union all select 5 union all select 7 union all select 8 union all select 9 union all select 10 union all select 11 union all select 12 union all select 13 union all select 14 union all select 15 union all select 16 union all select 17 go--原理的话你把select id=row_number() over (order by getdate()),num from tb 先执行一下对比看看,其实是等差数列的意味 select count(*) from ( select id=row_number() over (order by getdate()),num from tb )t group by num-id godrop table tb go/* 2 2 11
select col,id=identity(int,1,1)
into #tb
from tbselect count(*) cnt
from #tb
group by col-id
select col,row_number() over(order by col) as num from tab a
where not exists (
select 1 from tab
where col = a.col -1
)
),cte2 as (
select col,row_number() over(order by col) as num from tab a
where not exists (
select 1 from tab
where col = a.col +1
)
)
select @s = isnull(@s + ',','') + cast(cte2.col - cte1.col + 1 as varchar(10))
from cte1,cte2
where cte1.num = cte2.num
order by cte1.numprint @s
if object_id('tb','u') is not null
drop table tb
gocreate table tb(
num int
)
goinsert into tb(num)
select 1
union all select 2
union all select 4
union all select 5
union all select 7
union all select 8
union all select 9
union all select 10
union all select 11
union all select 12
union all select 13
union all select 14
union all select 15
union all select 16
union all select 17
go--原理的话你把select id=row_number() over (order by getdate()),num from tb
先执行一下对比看看,其实是等差数列的意味
select count(*) from (
select id=row_number() over (order by getdate()),num from tb
)t
group by num-id
godrop table tb
go/*
2
2
11