SQL查询在计数后不会返回零

Question

我是SQL新手，需要返回2018年或更晚开始的每个办公室的员工数量。对于没有任何员工在2018年1月1日之后开始工作的办公室，结果应包括零。

Employees
---------------------------------
|id|office_id|name    |start_date|
---------------------------------|
|1 | 1       | Kyle   |2018-08-08|
|2 | 1       | Sarah  |2017-02-28|
|3 | 3       | Moe    |2019-01-01|
|4 | 3       | Adi    |2018-11-04|
|5 | 3       | Kwun   |2016-05-22|
---------------------------------


Offices
---------
|id|name |
---------|
|1 | CHI |
|2 | NYC |
|3 | LA  |
---------

这将返回正确的计数，但我需要它为office id #2 (NYC)返回零。我遗漏了什么？

SELECT o.id, COUNT(e.id) AS NumberOfEmployees
FROM Offices AS o
LEFT JOIN Employees as e 
  ON office_id = o.id
WHERE start_date >= '2018-01-01'
GROUP BY o.id

Answer 1

您必须将start_date >= '2018-01-01'条件移到左连接中。这样，没有员工的办公室将可见：

SELECT o.id, COUNT(e.id) AS NumberOfEmployees
FROM Offices AS o
LEFT JOIN Employees as e 
  ON office_id = o.id 
      AND start_date >= '2018-01-01'
GROUP BY o.id