(一)group by having 使用场景
group by having 实现对数据的分类汇总,因此 group by 主要和统计类型计算函数一起使用,例如:count(),sum()等,而 having 是对我们统计的结果集再进行筛选,得到我们最后需要得到的数据集。不需要对统计的结果集 having 不则需要使用。
-- 按照员工编号统计员工在9月份的总工时、最长工作时间和工作天数
-- 然后筛选出总工时《176小时的员工
select
d.userId 员工编号,
d.userName as 员工名称,
sum(d.workTime) AS 总工时,
max(d.workTime) AS 最长工作时间,
count(d.dayTime) AS 工作天数
from
user_work d
where
d.createTime between "2020-09-01" and "2020-09-30"
group by userId
having 总工时 < 176
-- 按照员工编号统计员工在9月份的工作天数
select
d.userId 员工编号,
d.userName as 员工名称,
count(d.dayTime) AS 工作天数
from
user_work d
where
d.createTime between "2020-09-01" and "2020-09-30"
group by userId
(二)group by 去重
-- 统计9月份有哪些员工上班了
select
d.userId 员工编号,
d.userName as 员工名称
from
user_work d
where
d.createTime between "2020-09-01" and "2020-09-30"
group by userId