sql - 当 JOIN 没有返回值时检索 COUNT 为 0

Question

我有下表

性别

+----+-------------+
| ID | Description |
+----+-------------+
|  1 | M           |
|  2 | F           |
+----+-------------+

部门

+----+-------------------+
| ID |  DepartmentName   |
+----+-------------------+
|  1 | Application       |
|  2 | Change Management |
|  3 | Infrastructure    |
+----+-------------------+

员工

+----+----------+----------+-------------+--------------+
| ID |   Name   | GenderID | StaffNumber | DepartmentID |
+----+----------+----------+-------------+--------------+
|  1 | Stephen  |        1 | SC001       |            1 |
|  2 | Jacob    |        1 | SC002       |            1 |
|  3 | Maria    |        2 | SC003       |            1 |
|  4 | Valerie  |        2 | SC004       |            1 |
|  5 | Herman   |        1 | SC005       |            3 |
|  6 | Trevor   |        1 | SC006       |            3 |
|  7 | Mark     |        1 | SC007       |            2 |
|  8 | Hendrick |        1 | SC008       |            2 |
+----+----------+----------+-------------+--------------+

我想找出按部门和性别分组的员工总数。如果一个部门不存在性别，我仍然希望以 0 作为计数显示性别。

我试过这个 SQL，但它没有检索到我想要的:

SELECT
    e.DepartmentID,
    e.GenderID,
    COUNT(e.ID) AS TotalEmp
FROM
    Employee e
        RIGHT OUTER JOIN Gender g
            ON e.GenderID = g.ID
GROUP BY
    e.DepartmentID,
    e.GenderID
ORDER BY 
    e.DepartmentID,
    e.GenderID

当前结果

+--------------+----------+----------+
| DepartmentID | GenderID | TotalEmp |
+--------------+----------+----------+
|            1 |        1 |        2 |
|            1 |        2 |        2 |
|            2 |        1 |        2 |
|            3 |        1 |        2 |
+--------------+----------+----------+

预期结果

+--------------+----------+----------+
| DepartmentID | GenderID | TotalEmp |
+--------------+----------+----------+
|            1 |        1 |        2 |
|            1 |        2 |        2 |
|            2 |        1 |        2 |
|            2 |        2 |        0 |
|            3 |        1 |        2 |
|            3 |        2 |        0 |
+--------------+----------+----------+

Answer 1

试试这个:

   select d.DepartmentID,g.ID, COUNT(e.ID) AS TotalEmp
   from Gender g
   cross join Department d
   left join Employee e on e.genderid=g.id and e.departmentid=e.DepartmentID
   group by d.DepartmentID,g.id;