自查询一例：求连续出现的次数

 数据表如下：

 

 

 create table [tb]([value1] int,[value2] int) 
    insert [tb] 
    select 1,12 union all 
    select 1,13 union all 
    select 1,23 union all 
    select 0,14 union all 
    select 0,15 union all 
    select 1,16 union all 
    select 0,23 union all 
    select 0,22 union all 
    select 1,21 union all 
    select 1,12

 

 

 

 

求value1列中的数字连续出现的次数：

select * from tb

select identity(int,1,1) as id,* 
into #db
from tb

select * from #db

select a.value1,a.value2,nums=(
select count(1) from #db where value1=a.value1 
and id<=a.id
and id>=(select isnull(max(id),0) from #db where id<a.id and value1 !=a.value1)
)
from #db as a

drop table #db

 

结果如下：