表
型號 年 月 日
a 2013 01 01
a 2013 01 02
a 2013 01 04
a 2013 01 05
a 2013 01 06
我想統計a 2013年1月最多連續出現了多少次 有什麼辦法啊
3斷掉了 沒出現所以要重新計算 456 3天
------解决思路----------------------
select max(days) as 最大連續天數 from(
select MAX(id2)-MIN(id2)+1 as days from(
select cast(日 as int)-1 as id1,(select COUNT(1) from dt where 日<aa.日 and 型號='a' and 年='2013' and 月='01' ) as id2, from dt as aa where 型號='a' and 年='2013' and 月='01'
) as bb group by id1-id2
)cc
------解决思路----------------------
;WITH t1(num,yy,mm,dd)
AS
(
SELECT 'a', '2013', '01', '01' UNION ALL
SELECT 'a', '2013', '01', '02' UNION ALL
SELECT 'a', '2013', '01', '04' UNION ALL
SELECT 'a', '2013', '01', '05' UNION ALL
SELECT 'a', '2013', '01', '06'
)
,t2 AS
(
SELECT *,CAST(dd AS int) num1,ROW_NUMBER() OVER(PARTITION BY num,yy,mm ORDER BY dd) rn
FROM t1
--WHERE num='a' AND yy='2013' AND mm='01'
)
,t3 AS --给连续数分组
(
SELECT * ,1 AS partitionId FROM t2 WHERE rn=1
UNION ALL
SELECT t2.*,CASE WHEN t3.num1+1=t2.num1 THEN t3.partitionId
ELSE t3.partitionId+1 END AS partitionId
FROM t2 ,t3
WHERE t2.rn=t3.rn+1
)
--SELECT num,yy,mm,dd,partitionId
--FROM t3 --分完组想查什么查什么
SELECT MAX(cnt) FROM (SELECT COUNT(*) cnt FROM t3 GROUP BY partitionId) a
------解决思路----------------------
drop table t
CREATE TABLE [dbo].[t](
[xh] [varchar](20) NULL,
[rq] [varchar](20) NULL
) ON [PRIMARY]
truncate table t
insert into t values('a','2013-01-01')
insert into t values('a','2013-01-02')
insert into t values('a','2013-01-03')
insert into t values('a','2013-01-04')
insert into t values('a','2013-01-08')
insert into t values('a','2013-01-09')
insert into t values('a','2013-01-10')
insert into t values('a','2013-01-11')
insert into t values('a','2013-01-12')
insert into t values('a','2014-01-11')
insert into t values('a','2014-01-12')
select * from t
with temp as
(select ROW_NUMBER()over(order by xh)id,* from t) ,
temp1 as(
select ROW_NUMBER()over(order by t1.id)idnew, t1.* from temp t1 left join temp t2 on t1.id+1=t2.id where isnull(DATEDIFF(D,t1.rq,t2.rq),2)<>1
)
select MAX(c)[最大连续数] from (
select MIN(id)c,MIN(id)id from temp1
union all
select abs(t1.id-t2.id)c,t2.id id from temp1 t1 left join temp1 t2 on t1.idnew+1=t2.idnew)
b where id is not null