公司每天要进行打卡上班,每个人每天也可以多次打卡,所有打卡信息会记录到表A 中,表A中有name, date, time 等字段。
a. 要统计今天迟到的人的名单,请写出相应的SQL。
b. 每月会统计迟到次数排行前十的人的名单,请写出相应的SQL。
分析得之主要分以下三个子问题
1.迟到前十名
select `name`,count(`name`) as `sum`
from A
where (time>'09:00:00' or time is null)
group by `name`
order by `sum` desc
limit 0,10;
2.本月内
select *
from A
where date(now()) >= `date` and adddate(date(now()), -30) <= `date`;
3.获得每天最早打卡时间
1)在这里我是用的是临时表进行获取的,因为对于表中顺序不同,group by取得的第一条可能不是最早打卡时间,
我发现子查询不能解决这个问题,所以我在这里创建了临时表来保存time的asc顺序,然后再进行group by。
create temporary table t1(
select *
from A
order by `name`,`date`,`time`
);
select *
from t1
group by `date`,`name`;
drop table t1;
2)这里也可以通过建立索引的方式实现,对于order by和group by这种谓词需要排序的可以为其谓词建立索引,这里就是对
`name`,`date`,`time`建立索引。查询时候就不用再排序啦。
结合得出答案如下:
create temporary table t1(
select *
from A
order by `name`,`date`,`time`
);
select `name`,count(`name`) as `sum`
from (
select *
from t1
group by `date`,`name`) as b
where (time>'09:00:00' or time is null) and
(date(now()) >= `date` and adddate(date(now()), -30) <= `date`)
group by `name`
order by `sum` desc
limit 0,10;
drop table t1;