用mySQL的子查询+ SUM函数连接表

Question

这是我使用的提示符和代码。"查找所有项目工作时间超过20小时的女性员工姓名";

SELECT employee.ssn, employee.first_name, employee.last_name
FROM employee
WHERE employee.sex='F' AND employee.ssn IN (
SELECT working_hour.ssn
FROM working_hour
GROUP BY working_hour.ssn
HAVING SUM(working_hour.work_hour) > 20)
ORDER BY employee.first_name, employee.last_name;

现在我希望输出包含按员工分组的SUM(working_hour.work_hour)。但是我似乎找不到正确的cote来执行这个。

PS:它是否与working_hour表有复合键有关?

Answer 1

如果您想访问子查询中的字段，那么我将使用连接来重新表述:

SELECT e.ssn, e.first_name, e.last_name, wh.total_hours
FROM employee e
INNER JOIN
(
SELECT ssn, SUM(work_hour) AS total_hours
FROM working_hour
GROUP BY ssn
HAVING SUM(work_hour) > 20
) wh
ON wh.ssn = e.ssn
WHERE e.sex = 'F'
ORDER BY e.first_name, e.last_name;