题解 | #统计各岗位员工平均工作时长#

WITH staff_group AS(
    SELECT st.post,
            TIMESTAMPDIFF(SECOND, at.first_clockin, at.last_clockin) /(60*60) AS hours
    FROM attendent_tb AS at
    LEFT JOIN staff_tb AS st
    ON st.staff_id = at.staff_id
    GROUP BY st.post, hours
)
SELECT staff_group.post AS post,
        ROUND(AVG(hours), 3) AS work_hours
FROM staff_group
GROUP BY staff_group.post
ORDER BY work_hours DESC;