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

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

# 维度表放在后面再关联！