SQL Server 无需临时表查询同一部门同专业跨地点分布的去重列表

可直接运行的SQL语句（兼容所有MS SQL主流版本，无临时表） #

SELECT DISTINCT 
    e.Department AS 部门,
    e.Specialization AS 专业,
    e.Location AS 地点
FROM 员工表 e
WHERE EXISTS (
    SELECT 1
    FROM 员工表 e2
    WHERE e2.Department = e.Department
        AND e2.Specialization = e.Specialization
    GROUP BY e2.Department, e2.Specialization
    HAVING COUNT(DISTINCT e2.Location) >= 2
)
ORDER BY 部门, 专业, 地点

逻辑说明 #

1. 内层子查询先按部门、专业分组，筛选出满足「同部门同专业下不同办公地点数量≥2」的组合
2. 外层查询关联原表，取出上述组合对应的所有去重的部门、专业、地点数据
3. 全程未使用临时表，符合要求。如果你使用的是MS SQL 2017及以上版本，也可以用更简洁的IN写法：

SELECT DISTINCT 
    Department AS 部门,
    Specialization AS 专业,
    Location AS 地点
FROM 员工表
WHERE (Department, Specialization) IN (
    SELECT Department, Specialization
    FROM 员工表
    GROUP BY Department, Specialization
    HAVING COUNT(DISTINCT Location) >= 2
)
ORDER BY 部门, 专业, 地点

注：示例期望输出中专业列的Java是Java Lang的简写，如果需要截断输出可以按需加字符串处理函数即可。

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