SQL语句统计排行前10

哇，好久没来CSDN了，正好在前几天的面试题中，恰好遇到了自己的一个盲区，场景：公司每天要进行打卡上班，每个人每天也可以多次打卡，所有打卡信息会记录到表A 中，表A中有name, date, time 等字段。a. 要统计今天迟到的人的名单，请写出相应的SQL。b. 每月会统计迟到次数排行前十的人的名单，请写出相应的SQL。CREATE TABLE A(NAME VARCHAR(50),DATE

965201314.cn

13590人浏览 · 2021-02-18 19:11:56

965201314.cn · 2021-02-18 19:11:56 发布

SQL语句统计排行前10

引言

引言

场景：公司每天要进行打卡上班，每个人每天也可以多次打卡，所有打卡信息会记录到表A 中，表A中有name, date, time 等字段。
a. 统计当天迟到的人员名单，请写出相应的SQL。
b. 统计当月迟到次数排行前十的人员名单，请写出相应的SQL。

一、创建表

/**创建表：A*/
CREATE TABLE A(NAME VARCHAR(50),DATE DATE,TIME TIME);

二、插入数据

/*表A：插入数据*/
INSERT  INTO A(NAME,DATE,TIME) VALUES('张小小','2021-02-17','08:50:00');
INSERT  INTO A(NAME,DATE,TIME) VALUES('张小小','2021-02-17','09:50:00');
INSERT  INTO A(NAME,DATE,TIME) VALUES('张小小','2021-02-17','09:26:00');
INSERT  INTO A(NAME,DATE,TIME) VALUES('张小小','2021-02-17','09:12:00');
INSERT  INTO A(NAME,DATE,TIME) VALUES('赵晶晶','2021-02-17','09:13:00');
INSERT  INTO A(NAME,DATE,TIME) VALUES('赵晶晶','2021-02-17','09:14:00');
INSERT  INTO A(NAME,DATE,TIME) VALUES('赵金晶','2021-02-17','09:14:00');
INSERT  INTO A(NAME,DATE,TIME) VALUES('郭晓明','2021-02-17','09:12:00');
INSERT  INTO A(NAME,DATE,TIME) VALUES('郭晓明','2021-02-17','09:13:00');
INSERT  INTO A(NAME,DATE,TIME) VALUES('陈菲菲','2021-02-17','09:14:00');
INSERT  INTO A(NAME,DATE,TIME) VALUES('赵晶晶','2021-02-17','09:12:00');
INSERT  INTO A(NAME,DATE,TIME) VALUES('赵金晶','2021-02-17','09:13:00');
INSERT  INTO A(NAME,DATE,TIME) VALUES('陈小梅','2021-02-17','09:14:00');
INSERT  INTO A(NAME,DATE,TIME) VALUES('王小敏','2021-02-17','09:12:00');
INSERT  INTO A(NAME,DATE,TIME) VALUES('陈小梅','2021-02-17','09:13:00');
INSERT  INTO A(NAME,DATE,TIME) VALUES('童薇薇','2021-02-17','09:14:00');
INSERT  INTO A(NAME,DATE,TIME) VALUES('童薇薇','2021-02-17','09:12:00');
INSERT  INTO A(NAME,DATE,TIME) VALUES('童芳芳','2021-02-17','09:13:00');
INSERT  INTO A(NAME,DATE,TIME) VALUES('张小龙','2021-02-17','09:14:00');
INSERT  INTO A(NAME,DATE,TIME) VALUES('张小龙','2021-02-17','09:14:00');
INSERT  INTO A(NAME,DATE,TIME) VALUES('张大雷','2021-02-17','09:12:00');
INSERT  INTO A(NAME,DATE,TIME) VALUES('陈大雷','2021-02-17','09:13:00');
INSERT  INTO A(NAME,DATE,TIME) VALUES('陈大雷','2021-02-17','09:14:00');
INSERT  INTO A(NAME,DATE,TIME) VALUES('陈小凤','2021-02-17','09:12:00');
INSERT  INTO A(NAME,DATE,TIME) VALUES('李小风','2021-02-17','09:12:00');
INSERT  INTO A(NAME,DATE,TIME) VALUES('李西风','2021-02-17','09:13:00');
INSERT  INTO A(NAME,DATE,TIME) VALUES('李小风','2021-02-17','09:14:00');
INSERT  INTO A(NAME,DATE,TIME) VALUES('陈冬冬','2021-02-17','09:12:00');
INSERT  INTO A(NAME,DATE,TIME) VALUES('陈冬冬','2021-02-17','09:12:00');
INSERT  INTO A(NAME,DATE,TIME) VALUES('陈东东','2021-02-17','09:13:00');
INSERT  INTO A(NAME,DATE,TIME) VALUES('高小明','2021-02-17','09:14:00');
INSERT  INTO A(NAME,DATE,TIME) VALUES('高小明','2021-02-17','09:12:00');
INSERT  INTO A(NAME,DATE,TIME) VALUES('郭子怡','2021-02-17','09:12:00');
INSERT  INTO A(NAME,DATE,TIME) VALUES('高小凤','2021-02-17','09:12:00');

三、执行查询

这里，我们需要指定一个数据基准：
上班时间：9:00，九点之后打卡，均计算为迟到
当前时间：2021-02-17为例进行统计

1、统计当天迟到的人员名单

这个问题呢，需要注意一个点就好了，即：员工多次打卡以后怎么计算迟到次数就好了。实际上呢，排除掉九点(含九点)之前进行打卡的对象就好了！

SELECT NAME,COUNT(*) AS COUNT FROM A
GROUP BY NAME
HAVING NAME NOT IN(
/*排除当天：九点(含九点)前打卡，未迟到的人员*/
SELECT NAME FROM A WHERE TIME<='9:00' AND DATE='2021-02-17'
)
ORDER BY COUNT DESC

在这里插入图片描述

2、统计当月迟到次数排行前十的人员名单

这个问题其实也很简单，在我之前编写的过程中，并未考虑到多次打卡的情况，且Group By对象也有问题，修正后的版本如下：

/*统计所有迟到的员工*/
SELECT NAME,COUNT(*) AS COUNT FROM A
GROUP BY NAME
HAVING NAME NOT IN(
/*排除：九点(含九点)前打卡，未迟到的员工*/
SELECT DISTINCT(NAME) FROM A WHERE TIME<='9:00' AND NAME IN(
/*统计当前月(假定：2021-02月)迟到员工*/
SELECT t.name FROM (SELECT NAME,DATE,TIME, MONTH(DATE) 
AS MONTH,YEAR(DATE) AS YEAR FROM A WHERE TIME>'9:00' ) t WHERE t.year='2021' 
AND t.month=2))
/*前10逆序排序*/
ORDER BY COUNT DESC
LIMIT 10