mysql - sql 查询中带有总和聚合的 Where 子句

Question

我有一张表，我只想显示其中至少有一个注册用户的部门。我的查询如下:

SELECT departments.id as DepartmentID, 
       departments.depName as DepartmentName,
       SUM(IF(users.isDelete=0, 1, 0)) AS NumberOfUsers
FROM (myDB.departments) 
LEFT JOIN myDB.users ON departmentID=departments.id
AND `departments`.`isDelete` =  0
HAVING SUM(NumberOfUsers) >=0

HAVING SUM(NumberOfUsers) >=0 根本不起作用。我想检查 NumberOfUsers 是否大于 1 然后显示它，如果不大于 1 则不显示它。有什么建议吗？

Answer 1

试试这个:

SELECT departments.id as DepartmentID, 
       departments.depName as DepartmentName,
       (SELECT COUNT(u.id)
       FROM users u
       WHERE u.departmentID = d.id) as NumberOfUsers
FROM departments d
WHERE d.isdelete = 0
AND EXISTS
  (SELECT 'user'
  FROM users u
  WHERE u.departmentID = d.id)

这样，您就不会在主查询中使用 SUM(您必须使用 GROUP BY 来显示其他标量字段，如 departments.id 和 departments.depName。在 My Sql 中不是强制性的 GROUP BY)

EXISTS 子句保证至少存在一个用户。如果要显示所有部门(独立用户数，去掉EXISTS子句)