求一句SQL,求连续记录的最大个数
(2010-10-11 16:10:53)
标签:
杂谈 |
分类: DB |
链接:http://topic.csdn.net/u/20100402/17/1565fb1e-f1e0-4961-b0c4-ae41f95c550f.html
大家好,求一句SQL,求连续记录的最大个数
create table #t
(_id varchar(3), _date int)
insert #t select '001',20100101
union all select '001',20100102
union all select '002',20100103
union all select '002',20100101
union all select '002',20100102
union all select '002',20100104
union all select '001',20100105
union all select '001',20100106
union all select '001',20100107
union all select '002',20100105
union all select '002',20100107
union all select '002',20100108
union all select '003',20100101
union all select '003',20100102
union all select '003',20100104
union all select '003',20100105
union all select '003',20100106
union all select '003',20100108
union all select '003',20100109
union all select '003',20100111
要求: 求出每个ID 的最大连续记录的个数。记录有几百万, 效率要好一点。 谢谢!
结果为:001最大连续为从20100105到20100107,数量为3,
===================
001 3
002 5
003 3
===================
如果不影响效率的话,最好把时间段也带上:
=================================
001 3 20100105 20100107
002
003 3 20100104 20100106
=================================
要能加条件, 比如查询连续数量大于3的,结果只有一条
======================================
002
if OBJECT_ID('tempdb..#t')
is not null drop table #t
go
create table #t(_id varchar(3), _date int)
union all select '001',20100105
union all select '003',20100102
union all select '003',20100104
union all select '003',20100105
union all select '003',20100106
union all select '003',20100108
union all select '003',20100109
union all select '003',20100111
SELECT
RN=IDENTITY(INT),*
INTO # FROM
#T ORDER BY _ID,_DATE
SELECT
_ID,MAX(CNT)
MAXCNT
FROM
(
) AS T
GROUP BY _ID
DROP TABLE #