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

-- 思路
-- 先统计每个人的工作时长,然后做表连接。 3600秒等于1小时

WITH t AS(
    SELECT 
        staff_id
        ,ROUND(TIMESTAMPDIFF(SECOND, first_clockin, last_clockin)/3600, 3) AS t_diff
    FROM attendent_tb
)
-- t表的数据格式:
-- staff_id|t_diff|
-- --------+------+
--        1| 9.000|
--        2| 9.500|
--        3| 8.250|

SELECT 
    s.post
    ,AVG(t_diff) AS work_hours
FROM staff_tb s
    INNER JOIN t ON s.staff_id = t.staff_id
WHERE t.t_diff IS NOT NULL
GROUP BY s.post
ORDER BY work_hours DESC
;

END