PostgreSQL可用的子查询在SQL Server执行时报错求助

SQL Server不支持PostgreSQL中(Salary, Department) IN (...)这种多列元组匹配的语法，这是报错的核心原因——SQL Server会将(Salary,Department)识别为非布尔表达式，不符合WHERE子句的条件要求。

以下是三种可在SQL Server中正常执行的改写方案：

方案1：使用EXISTS子查询 #

通过EXISTS关联主表与子查询，用AND同时匹配薪资和部门：

SELECT * 
FROM Employee e
WHERE EXISTS (
    SELECT 1
    FROM Employee
    GROUP BY Department
    HAVING Department = e.Department 
       AND MAX(Salary) = e.Salary
)

方案2：使用INNER JOIN #

将分组后的最大薪资与部门结果作为临时集，和原表做关联查询：

SELECT e.*
FROM Employee e
INNER JOIN (
    SELECT MAX(Salary) AS MaxSalary, Department
    FROM Employee
    GROUP BY Department
) dept_sal 
    ON e.Department = dept_sal.Department 
    AND e.Salary = dept_sal.MaxSalary

方案3：使用窗口函数（推荐，适配同部门多员工薪资并列最高的场景） #

用ROW_NUMBER()按部门分区、薪资降序排序，筛选每个部门薪资最高的记录：

SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS rn
    FROM Employee
) ranked
WHERE rn = 1

如果需要保留部门内所有薪资并列最高的员工，可将ROW_NUMBER()替换为RANK()或DENSE_RANK()。

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