题解 | SQL221 统计各个部门的工资记录数

WITH t1 AS (
    SELECT 
        emp_no,
        dept_no 
    FROM dept_emp
    
),
t2 AS (
    SELECT 
        emp_no,
        COUNT(emp_no) AS sum 
    FROM salaries
    
    GROUP BY emp_no
),
t3 AS (
    SELECT 
        t1.dept_no,
        d.dept_name,
        SUM(t2.sum) AS sum
    FROM t2
    LEFT JOIN t1
    ON t2.emp_no = t1.emp_no
    LEFT JOIN departments d
    ON t1.dept_no = d.dept_no
    GROUP BY t1.dept_no,d.dept_name
    ORDER BY dept_no ASC
)
SELECT * FROM t3;

# 一个部门下有多个员工，因此统计部门要求和员工数量!