【大厂真题】SQL21题解 | 统计加班员工占比
# 需求分析:工作时长大于9.5小时定义为加班 # 加班员工占比(ratio,以百分数形式输出并保留1位小数),查询结果按照加班员工占比降序排序; # 时间相减 # 保留一位小数 SELECT a.department,CONCAT(ROUND(COUNT(b.staff_id)/COUNT(DISTINCT a.staff_id)*100,1),'%') as ratio from staff_tb a LEFT JOIN ( SELECT staff_id,TIMESTAMPDIFF(minute,first_clockin,last_clockin)/60 as times FROM attendent_tb where TIMESTAMPDIFF(minute,first_clockin,last_clockin)/60>9.5 ) b ON a.staff_id=b.staff_id GROUP BY a.department order by ratio desc