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

-- 三链表
SELECT d.dept_no, d.dept_name, COUNT(*) AS sum
FROM departments AS d, dept_emp AS de, salaries AS s
WHERE d.dept_no=de.dept_no
AND de.emp_no=s.emp_no
GROUP BY d.dept_no;

-- 我的方法
-- 有隐患，dept_no 和dept_name 要一一对应
SELECT  d1.dept_no,d1.dept_name,count(*) as sum
FROM 
(
    SELECT de.emp_no,d.dept_no,d.dept_name
    FROM departments as d inner join dept_emp as de
    ON d.dept_no = de.dept_no
) as d1 inner join salaries as s
on d1.emp_no = s.emp_no
group by dept_no
order by dept_no;