玖叶教程网

查询最大连续天数

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) maxDays
from
( 
select t2.custno
,BEGIN_day
,count(t2.BEGIN_day) as continous_days
from 
(
select 
custno,
jxdate,
rowid,-- 排序得到的序列号，如果减去后得到的结果是同一天，则说明是连续的；否则是不连续的。
DATE_Sub(jxdate,INTERVAL rowid DAY) as BEGIN_day
from
(
select custno
,jxdate
, ROW_NUMBER() OVER (PARTITION by custno order by jxdate) as rowid
from lianxu_max_days
)m 
)t2
group by t2.custno,t2.BEGIN_day
) z
group by custno;

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

----
如果每日日期有重复要取唯一
SELECT
custno,
min(jxdate) AS mindate,
max(jxdate) AS maxdate,
max(countday) countday,
group_concat(jxdate) dates
FROM
(
SELECT custno,
jxdate,
@countday := ( CASE WHEN ( DATEDIFF(jxdate ,@last_date)=1) THEN (@countday + 1) ELSE 1 END) AS countday,
@last_date :=jxdate
FROM(
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 赋值变量

------sqlserver
with t1 as with t1 as 
(
select distinct custno,jxdate
from lianxu_max_days 
)
select custno,max(nums1) maxdays
from 
(
select custno ,tempdt ,count(1) nums1
from 
(
select custno 
-- ,convert(char(8),dateadd(day,-rowid,jxdate),112) tempdt
,DATE_Sub(jxdate,INTERVAL rowid DAY) as tempdt
from 
(
select a.custno ,a.jxdate
,row_number() over (partition by a.custno order by a.jxdate) rowid
from t1 A
-- where a.custno='1000001640'
) C
) D
group by custno,tempdt
) E
group by custno