求最大连续时间问题

数据库求最大连续时间问题

问题:

用户每天访问页面会记录此用户的名字和访问当天日期(一天一次),求该用户本月最大连续访问的天数

创建语句:

CREATE TABLE test01(nname VARCHAR(10),
ddate INT)
INSERT INTO test01(nname,ddate) VALUES (‘张’,1);
INSERT INTO test01(nname,ddate) VALUES (‘张’,3);
INSERT INTO test01(nname,ddate) VALUES (‘张’,4);
INSERT INTO test01(nname,ddate) VALUES (‘张’,5);
INSERT INTO test01(nname,ddate) VALUES (‘张’,7);
INSERT INTO test01(nname,ddate) VALUES (‘张’,8);
INSERT INTO test01(nname,ddate) VALUES (‘张’,9);
INSERT INTO test01(nname,ddate) VALUES (‘张’,10);
INSERT INTO test01(nname,ddate) VALUES (‘张’,11);
INSERT INTO test01(nname,ddate) VALUES (‘张’,22);
INSERT INTO test01(nname,ddate) VALUES (‘李’,2);
INSERT INTO test01(nname,ddate) VALUES (‘李’,3);
INSERT INTO test01(nname,ddate) VALUES (‘李’,4);
INSERT INTO test01(nname,ddate) VALUES (‘李’,5);
INSERT INTO test01(nname,ddate) VALUES (‘李’,6);
INSERT INTO test01(nname,ddate) VALUES (‘李’,9);

解决sql:

SELECT NNAME,
max(t3.c)
FROM
(SELECT nname,
COUNT(t2.cha)AS c
FROM
(SELECT t1.nname,
t1.ddate,
t1.px,
(t1.ddate-t1.px)AS cha
FROM
(SELECT NNAME,
DDATE,
ROW_NUMBER()OVER(PARTITION BY NNAME
ORDER BY DDATE) AS px
FROM TEST01)t1)t2
GROUP BY t2.nname,
t2.cha)t3
GROUP BY t3.nname