题解 | SQL220 汇总各个部门当前员工的title类型的分配数目

WITH t1 AS (
	SELECT *
    FROM dept_emp
    WHERE to_date = '9999-01-01'
),
t2 AS (
	SELECT *
    FROM titles
    WHERE to_date = '9999-01-01'
),
t3 AS (
	SELECT
    	t1.dept_no,
    	a.dept_name,
    	t2.title,
	    count(t2.title) AS count
    FROM t2
    LEFT JOIN t1
    ON t2.emp_no = t1.emp_no
    LEFT JOIN departments a
    ON t1.dept_no = a.dept_no
    GROUP BY t1.dept_no,
    	a.dept_name,
    	t2.title
),
t4 AS (
	SELECT
    	title,
    	count(title) AS count
    FROM t3
    GROUP BY title
),
t5 AS (
	SELECT
    	t3.dept_no,
    	t3.dept_name,
    	t3.title,
    	t3.count
    FROM t3
    ORDER BY t3.dept_no,t3.title
)
SELECT * FROM t5;

# 主要count不是根据全局去计算，而是计算各自的情况！