检索每个部门获得更高工资的员工的姓名

Question

我有三个不同的表，比如SQL Server的department、employee和salary。

其结构

CREATE TABLE department (DeptId INT,DeptName Varchar(100)) 
INSERT INTO department VALUES (1,'Accounts'); 
INSERT INTO department VALUES (2,'Package'); 
CREATE TABLE employee (EmpId INT,DeptId INT,EmpName varchar(20)) 
INSERT INTO employee VALUES (1,1,'Sachin'); 
INSERT INTO employee VALUES (2,1,'Vikas'); 
INSERT INTO employee VALUES (3,2,'Sikha'); 
INSERT INTO employee VALUES (4,2,'Disha'); 
CREATE TABLE salary(EmpId INT,Sal int) 
INSERT INTO salary VALUES (1,400); 
INSERT INTO salary VALUES (2,700); 
INSERT INTO salary VALUES (3,700); 
INSERT INTO salary VALUES (4,900); 

结果将是：

DepName  | EmpName
-----------------
Accounts | Vikas
Package  | Disha

请帮助我，我需要一个查询来找到所需的结果。

我尝试了以下查询，但无法获得EmpName。

select DeptName, max(Sal) as Salary from 
(select dep.DeptName, emp.EmpName, sal.Sal from salary as sal
inner join employee emp on emp.EmpId = sal.EmpId 
inner join department dep on dep.DeptId = emp.DeptId) as tbls 
group by DeptName

Answer 1

您可以使用RANK()查询薪资。

以下是示例：

select DeptName
,EmpName 
from 
(
select  DeptName
,EmpName
,rank() over (partition by d.deptid order by sal desc) as ranknum
from employee as e
inner join salary as s
on e.EmpId=s.EmpId
left join department as d
on d.DeptId = e.DeptId
) as ranktable
where ranknum = 1

Answer 2

我们也可以使用CTE：

;WITH maxSalStaff AS (
SELECT 
rnk = ROW_NUMBER() OVER (PARTITION BY d.DeptId ORDER BY s.Sal DESC),
d.DeptName, 
e.EmpName,
s.Sal
--ItemID
FROM department d
INNER JOIN employee e ON e.DeptId = d.DeptId
INNER JOIN salary s ON s.EmpId = e.EmpId
)
SELECT * FROM maxSalStaff WHERE rnk = 1

Answer 3

尝试这个

SELECT T.EmpName, D.DeptName
FROM
(
SELECT E.EmpId, E.DeptId, E.EmpName, S.Sal, 
RANK() OVER (PARTITION BY E.DeptId ORDER BY S.Sal DESC)Rank
FROM employee E
INNER JOIN salary s ON E.EmpId = S.EmpId
)T 
INNER JOIN department D ON (T.DeptId = D.DeptId)
WHERE T.Rank=1

Answer 4

如果多个人可以拥有相同的薪水，请使用DENSE_RANK()。像下面的

WITH cteRowNum AS (
select 
e.EmpName,
d.DeptName,
sal,
DENSE_RANK() OVER (PARTITION BY DeptName order by sal desc) as RowNo
from employee e inner join
departments d on d.DeptId= e.DeptId inner join
salary s on e.EmpId= s.EmpId
)
SELECT EmpName, DeptName, Sal
FROM cteRowNum
WHERE RowNo = 1;