问题
公司每天要进行打卡上班,每个人每天也可以多次打卡,所有打卡信息会记录到表A 中,表A中有name, date, time 等字段。
问题一:要统计今天迟到的人的名单,请写出相应的SQL。
问题二:每月会统计迟到次数排行前十的人的名单,请写出相应的SQL。
解题
假设表结构如下
同时假设每天九点为该公司上班时间。
问题一解题思路:
条件:今天、迟到人、名单(人名、迟到时间)
分析:多次打卡,特殊情况下有人上班迟到前已经打卡,时间到后又打卡几次;因此,我们需要找到每个人第一次的打卡时间,判断此时间与上班时间的大小
#当天迟到人名单
SELECT `name`, MIN(`time`) as late_time from A
WHERE `date` = CURDATE()
GROUP BY `name`
HAVING MIN(`time`)>'09:00'
问题二解决思路:
条件:当月、迟到次数排名前十、名单(人名、迟到次数)
分析:在问题一的分析下,我们需要列出当月每个人的迟到打卡时间,然后计算每个人名字出现的次数,mysql下按次数降序排列取前十(oracle下可以用top 10)。
#本月迟到前十名,DATE_FORMAT(a.`date`, '%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m')
SELECT temp.`name`,count(temp.`name`) as late_count from (
SELECT a.`name`, MIN(a.`time`) as late_time
from A as a
WHERE Month(a.`date`) = Month(CURDATE())
GROUP BY a.`name`,a.`date`
HAVING MIN(a.`time`)>'09:00'
) as temp
GROUP BY temp.`name`
ORDER BY late_count DESC LIMIT 10