时间戳

首页 > 解决方案 > 检索各部门薪水较高的员工姓名

sql - 检索各部门薪水较高的员工姓名

问题描述

我有三个不同的表，如 SQL Server 的部门、员工和薪水。

它的结构

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

标签： sqlsql-servertsql

解决方案

可以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

推荐阅读