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

SELECT
    post,
    AVG(time_diff) work_hours
FROM 
    (SELECT
        st.post,
        ROUND(TIMESTAMPDIFF(MINUTE,at.first_clockin,at.last_clockin)/60,3) time_diff
    FROM staff_tb st
    JOIN attendent_tb at ON st.staff_id = at.staff_id
    WHERE at.first_clockin IS NOT NULL AND at.last_clockin IS NOT NULL
    )t1
GROUP BY post
ORDER BY work_hours DESC