MySQL多对多关联下如何查询满足共有房产条件的顶级经纪人

实现逻辑 #

• 自联结agent_properties表，匹配不同经纪人共同持有的房产
• 按经纪人对分组，筛选出共有房产数量≥2的有效配对
• 统计每个经纪人对应的有效配对其他经纪人数量，筛选出数量≥2的即为「顶级经纪人」

查询语句 #

SELECT agent_id
FROM (
    SELECT 
        a.agent_id, 
        b.agent_id AS other_agent_id
    FROM agent_properties a
    INNER JOIN agent_properties b 
        ON a.property_id = b.property_id 
        AND a.agent_id != b.agent_id
    GROUP BY a.agent_id, b.agent_id
    HAVING COUNT(DISTINCT a.property_id) >= 2
) AS valid_agent_pairs
GROUP BY agent_id
HAVING COUNT(DISTINCT other_agent_id) >= 2;

该语句基于提供的测试数据执行后，返回结果为1、3、5，完全符合预期判定规则。

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