mysql - SQL 数据库查询 : Count(Distinct)

Question

我正在处理 SQL 查询以执行以下操作:

For each project on which more than two employees work, retrieve the project number, the project name, and the number of employees who work on that project.

这是我目前的项目编号 1:

SELECT project.PNO, project.PNAME, COUNT(DISTINCT works_on.ESSN)
AS '# of employess worked on'

FROM project INNER JOIN works_on
ON project.PNO = works_on.PNO

INNER JOIN employee
ON works_on.ESSN = employee.SSN

HAVING COUNT(DISTINCT works_on.ESSN) > 2
AND project.PNO LIKE 1

它返回:

PNO:1

PNAME:ProductX

工作过的雇员人数:7

当它应该返回 NULL 时，因为项目编号 1 在 works_on 表中只有两个不同的 ESSN，而是返回 works_on 表中不同 ESSN 的总数

附件是我的架构:

Answer 1

您需要按项目获得结果，这意味着您缺少 group by 子句:

SELECT     project.PNO,
           project.PNAME, 
           COUNT(DISTINCT works_on.ESSN) AS '# of employess worked on'
FROM       project 
INNER JOIN works_on ON project.PNO = works_on.PNO
INNER JOIN employee ON works_on.ESSN = employee.SSN
GROUP BY   project.PNO, project.PNAME -- Here!
HAVING     COUNT(DISTINCT works_on.ESSN) > 2 AND project.PNO LIKE 1