SQL多个表，如果没有记录，则显示

Question

我有4个表(员工、组、部门、办公室)，希望有一个返回报告(报告)的SQL查询。

    Table: Workers
    +------------+----------+--------------+
    | workers_id | group_id | workers_name |
    +------------+----------+--------------+

    Table: Group
    +------------+------------+---------+-----------+
    | group_id   | group_name | dept_id | office_id |
    +------------+------------+---------+-----------+

    Table: Department
    +---------+----------+-----------+
    | dept_id | dept_name| office_id |
    +---------+----------+-----------+

    Table: Office
    +-----------+------------+
    | office_id | office_name|
    +-----------+------------+

我成功地使用了以下SQL语句，这将给出结果A：

    SELECT department.dept_name, count (workers.workers_id) as headcount,
    FROM workers, group, department
    WHERE workers.workers_id = group.group_id
    AND group.dept_id = department.dept_id
    GROUP BY group.group_id

    Result A: 
    +------------+----------+
    | dept_name  | headcount|
    +------------+----------+
    | dept A     | 2        |
    +------------+----------+
    | dept B     | 1        |
    +------------+----------+
    | dept D     | 2        |
    +------------+----------+
    | dept F     | 3        |
    +------------+----------+

然而，它没有列出有0名工人的部门(这是有意义的，因为没有)。是否仍然可以执行单个查询以获得以下结果？

    Result: 
    +------------+----------+
    | dept_name  | headcount|
    +------------+----------+
    | dept A     | 2        |
    +------------+----------+
    | dept B     | 1        |
    +------------+----------+
    | dept C     | 0        |
    +------------+----------+
    | dept D     | 2        |
    +------------+----------+
    | dept E     | 0        |
    +------------+----------+
    | dept F     | 3        |
    +------------+----------+

我尝试过isnull，左联合和联合，没有任何运气，我的sql-fu是基本水平。如果能帮上忙就好了。干杯!

Answer 1

由于您想要部门的员工，我认为您的意思是按dept_id分组(否则，COUNT将逐组计算员工，而不是部门)。当部门中有多个组时，这将导致不正确的结果)。另外，我想workers.workers_id = group.group_id上的连接不是故意的：

SELECT d.dept_name, COUNT(w.workers_id) AS headcount
    FROM Workers w
      JOIN Grp g ON w.group_id = g.group_id
      RIGHT JOIN Department d ON g.dept_id = d.dept_id
    GROUP BY d.dept_id;

NULL _workers_id_s在COUNT中不会被考虑在内。

注意:不要命名像关键字(GROUP)这样的表。

Answer 2

SELECT dept_name,
IF(count(workers_id) >0 ,count(*),0) AS headcount
FROM workers, group, department
WHERE workers.workers_id = group.group_id
AND group.dept_id = department.dept_id
GROUP BY group.group_id;

可以使用IF语句，IF语句的语法是..。

如果(结果为真，结果为假)