如何编写SQL查询统计关联表重复数据，筛选恰好拥有2套住房的人员ID

原有SQL存在的问题 #

你之前的写法存在两个核心错误：

• 聚合函数COUNT无法直接在WHERE子句中使用：WHERE子句的作用是在分组聚合前筛选行级数据，对聚合结果的过滤需要搭配GROUP BY子句，使用HAVING实现
• 子查询没有和外层的人员ID关联，统计的是全表的关联总条数，无法对应到单个人员的住房持有数量

正确实现写法 #

写法1：返回人员+对应2套住房的全部明细 #

-- 先统计出持有2套住房的人员ID
WITH qualified_person AS (
    SELECT person
    FROM intermediary
    GROUP BY person
    HAVING COUNT(home) = 2
)
-- 关联查询人员、住房明细
SELECT
    person.name AS '姓名',
    person.id AS '人员ID',
    home.location AS '住房地址'
FROM person
JOIN qualified_person ON person.id = qualified_person.person
JOIN intermediary ON person.id = intermediary.person
JOIN home ON intermediary.home = home.id

写法2：仅返回符合条件的人员ID和姓名（不需要住房明细） #

SELECT
    person.id AS '人员ID',
    person.name AS '姓名'
FROM person
JOIN intermediary ON person.id = intermediary.person
GROUP BY person.id, person.name
HAVING COUNT(intermediary.home) = 2

注意事项 #

如果中间表可能存在同一个人员绑定同一套住房的重复冗余数据，可以将COUNT(home)替换为COUNT(DISTINCT home)，避免重复计数导致结果不符合预期。

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