SQL Server 2016 关联多表查询掌握某国全部所需语言的人员列表

需求说明 #

现有3张表：Country、Language、People，Country和People无直接关联，两张表都通过外键Language_Id关联Language表。需要筛选出掌握的语言完全覆盖对应国家所有要求语言的人员。

表结构说明 #

1. People表（人员语言掌握表） #

2. Language表（语言字典表） #

3. Country表（国家要求语言表） #

预期输出结果 #

实现逻辑 #

核心判断规则：对每个国家先统计其要求的语言总数量，再统计每个人员掌握的、属于该国要求的语言数量，二者相等即说明该人员满足该国的语言要求。

SQL Server 2016 实现代码 #

SELECT DISTINCT
    p.Name,
    c.Name AS Country_Name
FROM
    People p
-- 先统计每个国家要求的语言总数
CROSS JOIN (
    SELECT Name, COUNT(DISTINCT Language_ID) AS required_lang_count
    FROM Country
    GROUP BY Name
) c
-- 匹配人员掌握的语言是否属于当前国家要求的语言
LEFT JOIN Country c_lang 
    ON c_lang.Name = c.Name AND p.Language_ID = c_lang.Language_ID
GROUP BY
    p.Name, c.Name, c.required_lang_count
-- 匹配到的语言数量等于该国要求的总语言数即为符合条件
HAVING
    COUNT(DISTINCT c_lang.Language_ID) = c.required_lang_count
ORDER BY
    p.Name, c.Name

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