编写一个SQL查询，查找IT部门工资排名前三的员工

Question

编写一个SQL查询，查找IT部门工资排名前三的员工。

我查询:

SELECT TOP(3) WITH TIES 
d.name AS department, e.name AS employee, e.salary 
FROM 
employee e, department d
WHERE 
e.departmentid = d.id AND d.name ='IT'
ORDER BY 
e.salary DESC

这应该显示我4个结果。因为我把领带也算进去了。但由于某些原因，事实并非如此。我不明白为什么。我需要前三名的薪水，包括领带。

我得到这样的结果:

900008500085000

为此您将需要窗口函数。SELECT TOP (n) WITH TIES在行n处停止，只有在存在平局时才包含更多行。

所以，除了适当的，明确的，标准的，可读JOIN语法，可以学习窗口函数:

SELECT e.*
FROM (SELECT d.name as department, e.name as employee, e.salary,
DENSE_RANK() OVER (ORDER BY e.salary DESC) as seqnum
FROM employee e JOIN
department d
ON e.departmentid = d.id and d.name = 'IT'
) e
WHERE seqnum <= 3
ORDER BY e.salary DESC