mssql sqlserver 使用sql脚本获取数据表中相同行出现的”连续次数”及每次”连续出现”的次数


摘要:
下文讲述统计相同行出现的连续次数,如下所示:
实验环境:sql server 2012


例:

  ---客户需求:
     CREATE TABLE [maomao365](
      Id int identity, sort varchar(36)
     )
 go 
/*
生成测试数据
*/
insert int  [maomao365]  
   (sort)values('sql','sql','sqlblog','sql','sql','sqlblog','sqlblog','sqlblog') 
 ---得到连续的sort每次连续出现的次数,如下结果:
go
 ;
with tmpTable as(
	select *,keyId=row_number ( ) OVER (order by id),keyId2=lead(sort)over(order by id)
	from [maomao365]
)
select [sort]
	,countAll=count(*)over(partition by sort)
	,[column]=keyId-isnull(lag(keyId)over(order by id),0)
from tmpTable
where sort<>isnull(keyId2,'')

name       countAll      column
---------- ----------- --------------------
sql         2           2
sqlblog     2           1
sql         2           2
sqlblog     2           3