SQL HAVING子句问题：无法查询出devices_count=0的用户

我一眼就看出问题出在哪了——你想用WHERE子句过滤devices_count=0的结果，但这个别名是COUNT()聚合函数计算出来的，而WHERE是在分组之前执行的，根本识别不了这个别名；就算你不用别名直接写COUNT(d.id)=0，WHERE也会把LEFT JOIN后d.id为NULL的行过滤掉，因为COUNT(d.id)在分组前还没完成计算呢。

给你两个实用的修正方案：

方案一：用HAVING子句过滤分组后的结果 #

把过滤条件放到HAVING里就对了，因为HAVING是在分组和聚合计算之后执行的，能正确识别聚合后的结果：

SELECT 
    `u`.`id` AS `usersId`, 
    `u`.`id_user` AS `usersIdUser`, 
    `u`.`name` AS `usersName`, 
    `u`.`postal` AS `usersPostal`, 
    `u`.`city` AS `usersCity`, 
    `u`.`street` AS `usersStreet`, 
    `g`.`name` AS `geographyName`, 
    `e`.`name` AS `expertsName`, 
    `e`.`surname` AS `expertsSurname`, 
    `o`.`name` AS `ownersName`, 
    COUNT(`d`.`id`) AS `devices_count` 
FROM `users` `u` 
LEFT JOIN `devices` `d` ON u.id = d.user_id  -- 这里假设关联条件是用户id关联设备表的user_id，你可根据实际表结构调整
LEFT JOIN `geography` `g` ON u.geography_id = g.id  -- 补充你没写完的关联逻辑
LEFT JOIN `experts` `e` ON u.expert_id = e.id
LEFT JOIN `owners` `o` ON u.owner_id = o.id
GROUP BY u.id, u.id_user, u.name, u.postal, u.city, u.street, g.name, e.name, e.surname, o.name
HAVING COUNT(`d`.`id`) = 0;

方案二：用子查询先筛选无设备的用户 #

如果觉得HAVING的性能不符合预期，也可以先找出没有关联设备的用户ID，再关联其他表，这样逻辑更直观：

SELECT 
    `u`.`id` AS `usersId`, 
    `u`.`id_user` AS `usersIdUser`, 
    `u`.`name` AS `usersName`, 
    `u`.`postal` AS `usersPostal`, 
    `u`.`city` AS `usersCity`, 
    `u`.`street` AS `usersStreet`, 
    `g`.`name` AS `geographyName`, 
    `e`.`name` AS `expertsName`, 
    `e`.`surname` AS `expertsSurname`, 
    `o`.`name` AS `ownersName`, 
    0 AS `devices_count` 
FROM `users` `u` 
LEFT JOIN `geography` `g` ON u.geography_id = g.id
LEFT JOIN `experts` `e` ON u.expert_id = e.id
LEFT JOIN `owners` `o` ON u.owner_id = o.id
WHERE u.id NOT IN (SELECT DISTINCT user_id FROM `devices` WHERE user_id IS NOT NULL);

额外注意点： #

• 方案一中，GROUP BY要包含所有非聚合的字段，不同数据库对GROUP BY的严格程度不同（比如MySQL开启ONLY_FULL_GROUP_BY模式时必须列全），所以最好把SELECT里所有非聚合字段都放到GROUP BY里。
• 方案二中，要给子查询加上WHERE user_id IS NOT NULL，不然NOT IN遇到NULL值会直接返回空结果。

内容的提问来源于stack exchange，提问作者kacper