统计一时间段内连续10天没打卡的用户

[lshfong]

表userlog

userid login_date
10001 2020-07-01
10001 2020-07-02
10001 2020-07-03
10002 2020-07-01
10003 2020-07-05
...
现在我想统计出连续10天都没有记录的用户，应该怎么样统计出来呢

[lshfong]

感谢热心解答，但是ID有好几W个，没办法每一个ID都去写select一次

[AHUA1001]

这个是样例数据，把你的表替换进来就好了。
把以下的TABLE_NAME ，直接换成你的表名就可以了。

SELECT userid,login_date2 未打卡开始时间,login_date1 未打卡结束时间,D 未打卡天数 FROM (
SELECT T1.userid,T1.login_date login_date1,T2.login_date login_date2,DATEDIFF(T1.login_date,T2.login_date) D FROM (
SELECT T.*,@R1:=@R1+1 R FROM (
SELECT T.* FROM TABLE_NAME T ORDER BY userid,login_date) T ,(SELECT @R1:=0) R) T1
LEFT JOIN (
SELECT T.*,@R2:=@R2+1 R FROM (
SELECT T.* FROM TABLE_NAME T ORDER BY userid,login_date) T ,(SELECT @R2:=0) R) T2
ON T2.USERID=T1.USERID AND T2.R=T1.R-1 ) T WHERE D>=10

[lshfong]

好象可以的。就是太复杂了，看起来有点吃力。结贴了分都给你，再次感谢！

[AHUA1001]

是从系统时间往前推算10天，还是认识已有的时间段，连续十天没有记录的。

[lshfong]

从已有时间段里进行统计，比如指定一个时间段，在这个时间段里计算

[AHUA1001]

找一个mysql数据库，直接执行以下语句。

SELECT userid,login_date2 未打卡开始时间,login_date1 未打卡结束时间,D 未打卡天数 FROM (
SELECT T1.userid,T1.login_date login_date1,T2.login_date login_date2,DATEDIFF(T1.login_date,T2.login_date) D FROM (
SELECT T.*,@R1:=@R1+1 R FROM (
SELECT T.* FROM (
SELECT '10001' userid ,'2020-07-01' login_date FROM DUAL UNION ALL
SELECT '10001','2020-07-02' FROM DUAL UNION ALL
SELECT '10001','2020-07-03' FROM DUAL UNION ALL
SELECT '10002','2020-07-01' FROM DUAL UNION ALL
SELECT '10003','2020-07-05' FROM DUAL UNION ALL
SELECT '10001','2020-08-02' FROM DUAL UNION ALL
SELECT '10001','2020-08-03' FROM DUAL UNION ALL
SELECT '10002','2020-08-01' FROM DUAL ) T ORDER BY userid,login_date) T ,(SELECT @R1:=0) R) T1
LEFT JOIN (
SELECT T.*,@R2:=@R2+1 R FROM (
SELECT T.* FROM (
SELECT '10001' userid ,'2020-07-01' login_date FROM DUAL UNION ALL
SELECT '10001','2020-07-02' FROM DUAL UNION ALL
SELECT '10001','2020-07-03' FROM DUAL UNION ALL
SELECT '10002','2020-07-01' FROM DUAL UNION ALL
SELECT '10003','2020-07-05' FROM DUAL UNION ALL
SELECT '10001','2020-08-02' FROM DUAL UNION ALL
SELECT '10001','2020-08-03' FROM DUAL UNION ALL
SELECT '10002','2020-08-01' FROM DUAL ) T ORDER BY userid,login_date) T ,(SELECT @R2:=0) R) T2
ON T2.USERID=T1.USERID AND T2.R=T1.R-1 ) T WHERE D>=10




把以下内容，直接替换成你的表名，就可以了。

SELECT '10001' userid ,'2020-07-01' login_date FROM DUAL UNION ALL
SELECT '10001','2020-07-02' FROM DUAL UNION ALL
SELECT '10001','2020-07-03' FROM DUAL UNION ALL
SELECT '10002','2020-07-01' FROM DUAL UNION ALL
SELECT '10003','2020-07-05' FROM DUAL UNION ALL
SELECT '10001','2020-08-02' FROM DUAL UNION ALL
SELECT '10001','2020-08-03' FROM DUAL UNION ALL
SELECT '10002','2020-08-01' FROM DUAL

[AHUA1001]

难了不会，会了不难。写代码，都是从能看懂别人的代码开始的。