MySQL:查询应返回计数为 0 的行

Question

我有 2 个表 - 用户和部门以及按部门返回用户计数的查询。如果用户没有返回“无部门”的部门。但我还需要没有用户的部门作为计数0。这是我的查询:

SELECT COALESCE(departments.name, 'No department') AS name, count( * ) AS count
FROM users
LEFT JOIN departments ON departments.id = users.department_id
WHERE users.is_deleted = 0
AND users.company_id = 1
AND (TIMESTAMPDIFF(YEAR, `date_of_birth`, CURDATE()) BETWEEN 1 AND 18 )
GROUP BY departments.name

它应该是这样的:

________________________
  Dep name  |  count    |
________________________
Dep 1       | 2         |
________________________
Dep 2       | 3         |
________________________
Dep 3       | 0         | if users  in this department not exist
________________________
No dep      | 1         | if users not have department
________________________

伙计们，请帮帮我!

我找到了解决方案

SELECT   COALESCE(locations.name, 'Without location')  AS location,
            COUNT(IF(TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) BETWEEN 1 AND 17, 1, NULL)) 'group_1_17'

          FROM users
          LEFT JOIN locations ON locations.id = users.location_id
          WHERE users.is_deleted = 0 AND users.company_id = :company_id
          GROUP BY locations.name

          UNION

          SELECT
            locations.name AS location,
            0 'group_1_17'         
          FROM users
          RIGHT JOIN locations ON locations.id = users.location_id
          WHERE locations.company_id = :company_id AND users.id IS NULL"

Answer 1

having count(*) = 0

这是聚合的“位置”。

SELECT COALESCE(departments.name, 'No department') AS name, count( * ) AS count
FROM users
LEFT JOIN departments ON departments.id = users.department_id
WHERE users.is_deleted = 0
AND users.company_id = 1
AND (TIMESTAMPDIFF(YEAR, `date_of_birth`, CURDATE()) BETWEEN 1 AND 18 )
GROUP BY departments.name
HAVING COUNT(*) = 0