一个技术博客

Mysql 求连续天数

本文由 Tim 于 2016-03-30 15:28:24 发表

表结构数据如下,一个记录用户登录的表,需要计算连续天数

需要实现如下结果
姓名 连续天数 开始日期 结束日期
张三 3 2015-05-18 2015-05-20
张三 5 2015-12-20 2015-12-24
李四 2 2016-01-10 2016-01-11
李四 3 2016-01-13 2016-01-15

 

逻辑是用一个比数据中都早的日期减去记录里的日期DATEDIFF(‘1900-01-01′,DATE_FORMAT(lo.login_time,’%Y-%m-%d’))求出此值(A)后,
计算select count(1) from login lo1 where lo1.uid=lo.uid and DATEDIFF(lo1.login_time,lo.login_time)>=0 即统计表中相同用户下比login_time小的值(B)
先拆解sql语句,输出A和B

 

可得到
uid username A B
1 张三 -42140 8
1 张三 -42141 7
1 张三 -42142 6
1 张三 -42356 5
1 张三 -42357 4
1 张三 -42358 3
1 张三 -42359 2
1 张三 -42360 1
2 李四 -42377 5
2 李四 -42378 4
2 李四 -42380 3
2 李四 -42381 2
2 李四 -42382 1

之后使用A-B得到rd,如果rd值一样,则天数是连续的,最后使用group by即可。

最终sql语句如下

uid username 连续天数 开始时间 结束时间 rd
1 张三 3 2015-05-18 2015-05-20 -42148
1 张三 5 2015-12-20 2015-12-24 -42361
2 李四 2 2016-01-10 2016-01-11 -42382
2 李四 3 2016-01-13 2016-01-15 -42383

如果有重复的数据还需要先剔重再计算,读者可以自行尝试。

写到这里想到了一个比较简单查询考勤异常的

表结构数据如下,一个记录员工打卡的表,需要查出考勤异常的,假设8:30上班,17:30下班

 

逻辑比较简单直接给出sql语句了

uid  username    日期               min                          max
1        张三    2015-05-18  2015-05-18 08:49:00  2015-05-18 16:39:00
1        张三    2015-12-21  2015-12-21 08:01:00  2015-12-21 08:01:00
1        张三    2015-12-22  2015-12-22 08:01:00  2015-12-22 17:01:00
1        张三    2015-12-24  2015-12-24 08:01:00  2015-12-24 08:01:00
2        李四    2016-01-13  2016-01-13 08:01:00  2016-01-13 08:01:00

就是利用按日期分组取最小值和最大值进行条件查询,如果还需要统计没打卡可以用用户表,日期用distinct生成(也可以单独建立一个日期表)作为主表后left join

 

欢迎转载,转载请注明出处!Tim » Mysql 求连续天数

点赞 (1)or拍砖 (0)
分享到:更多 ()