【大厂真题】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