查询最大连续天数

CREATE TABLE `lianxu_max_days`

(

`custno` varchar(10) CHARACTER SET gb18030 COLLATE gb18030_bin DEFAULT NULL,

`jxdate` varchar(8) COLLATE gb18030_bin DEFAULT NULL

)

数据

-----mysql----如果每日日期有重复要取唯一select custno,max(continous_days) maxDaysfrom( select t2.custno,BEGIN_day,count(t2.BEGIN_day) as continous_daysfrom (select custno,jxdate,rowid,-- 排序得到的序列号，如果减去后得到的结果是同一天，则说明是连续的；否则是不连续的。DATE_Sub(jxdate,INTERVAL rowid DAY) as BEGIN_dayfrom(select custno,jxdate, ROW_NUMBER() OVER (PARTITION by custno order by jxdate) as rowidfrom lianxu_max_days)m )t2group by t2.custno,t2.BEGIN_day) zgroup by custno;

-----mysql----如果每日日期有重复要取唯一select custno,max(continous_days) maxDaysfrom( select t2.custno,BEGIN_day,count(t2.BEGIN_day) as continous_daysfrom (select custno,jxdate,rowid,-- 排序得到的序列号，如果减去后得到的结果是同一天，则说明是连续的；否则是不连续的。DATE_Sub(jxdate,INTERVAL rowid DAY) as BEGIN_dayfrom(select custno,jxdate, ROW_NUMBER() OVER (PARTITION by custno order by jxdate) as rowidfrom lianxu_max_days)m )t2group by t2.custno,t2.BEGIN_day) zgroup by custno;

----如果每日日期有重复要取唯一SELECTcustno,min(jxdate) AS mindate,max(jxdate) AS maxdate,max(countday) countday,group_concat(jxdate) datesFROM(SELECT custno,jxdate,@countday := ( CASE WHEN ( DATEDIFF(jxdate ,@last_date)=1) THEN (@countday + 1) ELSE 1 END) AS countday,@last_date :=jxdateFROM(SELECT custno,jxdate FROM lianxu_max_days ORDER BY custno,jxdate) AS t1,(select @countday:=0,@last_date:='') as t2) AS t3 GROUP BY custno@countday @last_date 这种是变量声明:= 赋值@countday := ( CASE WHEN ( DATEDIFF(date ,@last_date) = 1) THEN (@countday + 1) ELSE 1 END当两个时间差值为1的时候,则为连续天数，@countday + 1 变量加一，否则为1 结束@last_date := date 将data赋值变量@last_date所以每次比较的都是当前行data和上一行data比较。(select @countday:=0,@last_date:=’’) as 赋值变量

------sqlserverwith t1 as with t1 as (select distinct custno,jxdatefrom lianxu_max_days )select custno,max(nums1) maxdaysfrom (select custno ,tempdt ,count(1) nums1from (select custno -- ,convert(char(8),dateadd(day,-rowid,jxdate),112) tempdt,DATE_Sub(jxdate,INTERVAL rowid DAY) as tempdtfrom (select a.custno ,a.jxdate,row_number() over (partition by a.custno order by a.jxdate) rowidfrom t1 A-- where a.custno='1000001640') C) Dgroup by custno,tempdt) Egroup by custno