sql - 一个更好的查询来查找部门中薪水最高的员工列表？

Question

我有一张表:[tblEmp]

EmpId | EmpName | DeptId

和一张表[tblSalary]

EmpId | Salary

我需要找到部门中薪水最高的员工列表。

我可以通过以下方式实现:

SELECT *
FROM tblEmp
JOIN tblSal ON tblSal.EmpId = tblEmp.EmpId
WHERE LTRIM(STR(deptid)) + LTRIM(STR(salary)) IN (
    SELECT LTRIM(STR(deptid)) + LTRIM(STR(MAX(salary)))
    FROM tblSal
    JOIN tblEmp ON tblSal.EmpId = tblEmp.EmpId
    GROUP BY DeptId
)

有没有更好的方法实现列表？

Answer 1

您可以尝试使用 ROW_NUMBER .

Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

有点像

;WITH Employees AS (
        SELECT  e.*,
                ROW_NUMBER() OVER(PARTITION BY e.DeptId ORDER BY s.salary DESC) RowID
        FROM    [tblEmp] e INNER JOIN
                [tblSalary] s ON    e.EmpId = s.EmpId
)
SELECT  *
FROM    Employees
WHERE   RowID = 1

但是，这不会返回同一部门中薪水相同的员工。

为此，您可能需要查看 RANK (Transact-SQL)或 DENSE_RANK (Transact-SQL)而不是 ROW_NUMBER。

Rank : Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.

If two or more rows tie for a rank, each tied rows receives the same rank.

Dense_Rank : Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.