SQL优化：如何高效查询仅指定用户访问过的关联城镇？

需求与表结构 #

需求：找出仅George（ID=1）和Joe（ID=2）去过、其他用户未去过的城镇ID。

TABLE_1（用户表） #

LINK_TABLE（用户-城镇关联表） #

TABLE_2（城镇表） #

现有尝试及问题 #

尝试语句1 #

SELECT DISTINCT ID2 FROM LINK_TABLE WHERE ID1 IN (1, 2)

• 问题：返回George和Joe去过的所有城镇，但包含其他用户（如Patrick）也去过的城镇（比如New York）。

尝试语句2 #

SELECT DISTINCT ID2 
FROM LINK_TABLE 
WHERE ID2 IN (
        SELECT DISTINCT ID2 
        FROM LINK_TABLE 
        WHERE ID1 IN (1, 2)
    ) AND ID1 NOT IN (1,2)

• 问题：返回的是Patrick去过且George和Joe也去过的城镇，完全不符合需求。

尝试语句3 #

SELECT DISTINCT ID2 
FROM LINK_TABLE 
WHERE ID1 IN (1, 2) AND ID2 NOT IN (
    SELECT DISTINCT ID2 FROM LINK_TABLE WHERE ID2 IN (
       SELECT DISTINCT ID2 FROM LINK_TABLE WHERE ID1 IN (1, 2)
    ) AND ID1 NOT IN (1,2)) 

• 问题：结果符合预期，但多层嵌套子查询导致可读性差，大表场景下可能存在性能瓶颈。

优化方案：分组聚合筛选 #

简洁实现版 #

SELECT ID2
FROM LINK_TABLE
GROUP BY ID2
HAVING 
    COUNT(DISTINCT ID1) = 2
    AND MAX(ID1) = 2
    AND MIN(ID1) = 1;

直观易懂版 #

SELECT ID2
FROM LINK_TABLE
GROUP BY ID2
HAVING 
    -- 排除有其他用户访问的城镇
    SUM(CASE WHEN ID1 NOT IN (1,2) THEN 1 ELSE 0 END) = 0
    -- 确保George（ID=1）去过该城镇
    AND SUM(CASE WHEN ID1 = 1 THEN 1 ELSE 0 END) >= 1
    -- 确保Joe（ID=2）去过该城镇
    AND SUM(CASE WHEN ID1 = 2 THEN 1 ELSE 0 END) >= 1;

关联城镇名称的完整查询 #

如果需要同时获取城镇名称，可以关联TABLE_2：

SELECT t2.ID, t2.NAME
FROM LINK_TABLE lt
JOIN TABLE_2 t2 ON lt.ID2 = t2.ID
GROUP BY t2.ID, t2.NAME
HAVING 
    SUM(CASE WHEN lt.ID1 NOT IN (1,2) THEN 1 ELSE 0 END) = 0
    AND SUM(CASE WHEN lt.ID1 = 1 THEN 1 ELSE 0 END) >= 1
    AND SUM(CASE WHEN lt.ID1 = 2 THEN 1 ELSE 0 END) >= 1;

方案优势 #

• 可读性强：通过分组聚合的逻辑直接表达需求，避免多层嵌套子查询的混乱。
• 性能更优：利用分组聚合+过滤的方式，若在LINK_TABLE的ID2和ID1字段建立联合索引，查询效率会显著提升。

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