根据列值分组、计数和排除

Question

虽然我在 SQL 或 MySQL 方面并不是完全的新手，但我注意到还有很多东西需要学习。经过多次尝试、阅读和搜索后，我无法理解这个问题。如果您能给我任何指点，我将不胜感激。

我已将实际数据和表格简化如下。

有两个相关表：Staff 和 Work。它们包含各个项目的人员数据。

员工：

ID  Name    Unit     

1   Smith   Chicago
2   Clarke  London
3   Hess    Chicago

工作：

StaffID   ProjectID

1          10
2          10
3          10
1          20
2          30
3          40
1          50
3          50

目标：

将所有有芝加哥员工的项目分组，并统计该项目中的所有员工数量。

预期结果：

Project  Staff count

10        3
20        1
40        1
50        2

因此项目 30 未列出，因为其成员不是来自芝加哥。

我下面的查询显然是错误的。它只计算来自芝加哥的项目成员，而不计算整个项目工作人员。

SELECT 
    work.projectID as Project, COUNT(*) as "Staff count" 
FROM 
    staff 
JOIN
    work ON staff.ID=work.staffID
WHERE
    unit="Chicago"
GROUP BY
    work.projectID;

Answer 1

我会将芝加哥的测试放在子选择中。
或者，您可以使用自连接，但我发现子选择更容易理解。

SELECT 
  w.projectid as project
  ,COUNT(*) as `staff count`
FROM work w
INNER JOIN staff s ON (w.staffID = s.id)
WHERE w.projectID IN (
  SELECT w2.projectID FROM work w2
  INNER JOIN staff s2 ON (w2.staffID = s2.id AND s2.unit = 'Chicago'))
GROUP BY w.projectid

Answer 2

删除 where 子句并添加having 子句，该子句检查至少一名员工来自芝加哥。

SELECT 
    work.projectID as Project, COUNT(*) as "Staff count" 
FROM 
    staff 
JOIN
    work ON staff.ID=work.staffID
GROUP BY
    work.projectID
HAVING
    count(case unit when 'Chicago' then 1 end) > 0;

Answer 3

最后：结果。再次感谢@Johan 和@a'r 的帮助，并感谢@Johan 让我走上正轨（就我而言）。

我将子选择更改为派生表，并将其与 projectID 上的工作表进行内连接。

SELECT 
   w.projectID AS project
   ,COUNT(*) AS `staff count`
FROM work w
INNER JOIN 
    (SELECT DISTINCT w2.projectID 
        FROM work w2
        INNER JOIN staff s ON (w2.staffID = s.id AND s.unit = 'Chicago')) c
ON (w.projectID = c.projectID)
GROUP BY w.projectID