日期:2014-05-18  浏览次数:20450 次

求连续值得行数统计
表aa结构如下:
----------
bm GM(数字)
----------
201101 0
201102 1
201103 1
201104 1
201105 1
201106 0
201107 0
201108 0
200109 1
----------
我要查询连续的1 或0 的最大连续数量
以上最大的连续数量为4 ,如何用sql语句查询出来

------解决方案--------------------
SQL code
if object_id('[TB]') is not null drop table [TB]
go
create table [TB] (bm int,GM int)
insert into [TB]
select 201101,0 union all
select 201102,1 union all
select 201103,1 union all
select 201104,1 union all
select 201105,1 union all
select 201106,0 union all
select 201107,0 union all
select 201108,0 union all
select 200109,1

select * from [TB]


select TB.gm,(MAX(TB.bm)-MIN(B.bm))+1  --加1是弥补本身起始值
from TB
inner join TB B on TB.bm = B.bm+1 and TB.GM = B.gm
group by TB.gm

/*
0    3
1    4*/