如何用SQL实现多表关联，获取包含指定兴趣子集的条目？

要筛选出**包含对应页面所有兴趣（允许条目有额外兴趣）**的条目，核心思路是：先统计每个页面的兴趣总数，再统计条目与页面共同匹配的兴趣数，当两者相等时，说明条目覆盖了页面的全部兴趣子集。

方法一：子查询实现 #

SELECT 
    pi.page_id,
    ii.item_id
FROM 
    pages_interests pi
JOIN 
    items_interests ii ON pi.interest_id = ii.interest_id
GROUP BY 
    pi.page_id, ii.item_id
HAVING 
    COUNT(DISTINCT pi.interest_id) = (
        SELECT COUNT(DISTINCT interest_id) 
        FROM pages_interests 
        WHERE page_id = pi.page_id
    )
ORDER BY 
    pi.page_id, ii.item_id;

代码说明 #

1. 关联表：通过JOIN匹配页面和条目共同拥有的兴趣记录，得到所有可能的页面-条目兴趣匹配对。
2. 分组统计：按page_id和item_id分组，统计每组中匹配的兴趣数量（DISTINCT用于避免同个兴趣重复计数，保证统计准确性）。
3. 筛选条件：HAVING子句中的子查询会计算当前页面的总兴趣数，只有当匹配的兴趣数等于页面总兴趣数时，才说明该条目包含了页面的全部兴趣，符合要求。
4. 排序输出：按页面ID和条目ID排序，得到清晰的结果。

方法二：CTE预计算优化（大数据量推荐） #

如果数据量较大，用CTE预计算每个页面的兴趣总数，避免子查询重复执行，性能更优：

WITH page_interest_counts AS (
    SELECT 
        page_id,
        COUNT(DISTINCT interest_id) AS interest_count
    FROM pages_interests
    GROUP BY page_id
)
SELECT 
    pi.page_id,
    ii.item_id
FROM 
    pages_interests pi
JOIN 
    items_interests ii ON pi.interest_id = ii.interest_id
JOIN 
    page_interest_counts pic ON pi.page_id = pic.page_id
GROUP BY 
    pi.page_id, ii.item_id, pic.interest_count
HAVING 
    COUNT(DISTINCT pi.interest_id) = pic.interest_count
ORDER BY 
    pi.page_id, ii.item_id;

验证结果 #

执行上述任意一段SQL，都会得到预期输出：

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