26

题目：汇总各个部门当前员工的title类型的分配数目，结果给出部门编号dept_no、dept_name、其当前员工所有的title以及该类型title对应的数目count，当前是指’9999-01-01’
隐含要求：dept_no按照升序排列

错误示范：SELECT里面的子查询出现了两个列：title和count()
SELECT d.dept_no, d.dept_name,
(SELECT t.title, count()
FROM dept_emp AS de
INNER JOIN titles AS t
ON de.emp_no=t.emp_no
WHERE de.to_date='9999-01-01'
AND t.to_date='9999-01-01'
AND de.dept_no=d.dept_no
GROUP BY t.title
)
FROM departments AS d

错误示范：虽然SELECT里面的子查询只出现了一个列：count()，但是子查询会返回多个值，所以仍然不可行(必须只返回单值)
SELECT d.dept_no, d.dept_name,
(SELECT count()
FROM dept_emp AS de
INNER JOIN titles AS t
ON de.emp_no=t.emp_no
WHERE de.to_date='9999-01-01'
AND t.to_date='9999-01-01'
AND de.dept_no=d.dept_no
GROUP BY t.title
)
FROM departments AS d

改进版本：我感觉是对的，在mysql中运行的结果也没问题，但是在题库中无法通过(标黄为核心代码)
SELECT DISTINCT d.dept_no, d.dept_name, t.title,
(SELECT COUNT(*)
FROM dept_emp AS de2
INNER JOIN titles AS t2
ON de2.emp_no=t2.emp_no
WHERE de2.to_date='9999-01-01'
AND t2.to_date='9999-01-01'
AND de2.dept_no=d.dept_no
AND t2.title=t.title) AS 'count'
FROM departments AS d, dept_emp AS de, titles AS t
WHERE d.dept_no=de.dept_no
AND de.emp_no=t.emp_no
AND de.to_date='9999-01-01'
AND t.to_date='9999-01-01'
ORDER BY d.dept_no;
注意：这个DISTINCT特别容易忽略
mysql运行结果：
+---------+--------------------+-----------------+-------+
| dept_no | dept_name | title | count |
+---------+--------------------+-----------------+-------+
| d001 | Marketing | Senior Engineer | 1 |
| d001 | Marketing | Staff | 1 |
| d002 | Finance | Senior Engineer | 1 |
| d003 | Human Resources | Senior Staff | 1 |
| d004 | Production | Senior Engineer | 2 |
| d005 | Development | Senior Staff | 1 |
| d006 | Quality Management | Engineer | 2 |
| d006 | Quality Management | Senior Engineer | 1 |
+---------+--------------------+-----------------+-------+

能通过题库的方法：GROUP BY dept_no和title
SELECT d.dept_no, d.dept_name, t.title, COUNT(*) AS count
FROM departments AS d, dept_emp AS de, titles AS t
WHERE d.dept_no=de.dept_no
AND de.emp_no=t.emp_no
AND de.to_date='9999-01-01'
AND t.to_date='9999-01-01'
GROUP BY d.dept_no, t.title;
补充：WHERE的三表联查也可以改成两个内连接
mysql运行结果：
+---------+--------------------+-----------------+-------+
| dept_no | dept_name | title | count |
+---------+--------------------+-----------------+-------+
| d001 | Marketing | Senior Engineer | 1 |
| d001 | Marketing | Staff | 1 |
| d002 | Finance | Senior Engineer | 1 |
| d003 | Human Resources | Senior Staff | 1 |
| d004 | Production | Senior Engineer | 2 |
| d005 | Development | Senior Staff | 1 |
| d006 | Quality Management | Engineer | 2 |
| d006 | Quality Management | Senior Engineer | 1 |
+---------+--------------------+-----------------+-------+