题解 | 统计加班员工占比

 SELECT department,concat(format(avg(CASE WHEN duration > 570 THEN 1 ELSE 0 END)*100,1),'%') AS ratio
 FROM(   
    SELECT a.staff_id,a.department,round(timestampdiff(MINUTE,b.first_clockin,last_clockin),1) AS duration
    FROM staff_tb AS a
    JOIN attendent_tb AS b
    ON a.staff_id = b.staff_id
 ) AS c
 GROUP BY department
 ORDER BY ratio DESC

此题ez，先将两表连接起来保留id(应该可以不要)，部门和时间,然后用GROUP BY 和AVG聚合函数就o而k之了