• 主页

# 帮朋友发贴：找出每个部门获得前三高工资的所有员工

``````/*
Employee 表包含所有员工信息，每个员工有其对应的工号 Id，姓名 Name，

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 85000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
| 5  | Janet | 69000  | 1            |
| 6  | Randy | 85000  | 1            |
| 7  | Will  | 70000  | 1            |
+----+-------+--------+--------------+
Department 表包含公司所有部门的信息。
+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Randy    | 85000  |
| IT         | Joe      | 85000  |
| IT         | Will     | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
+------------+----------+--------+

IT 部门中，Max 获得了最高的工资，Randy 和 Joe 都拿到了第二高的工资，
Will 的工资排第三。销售部门（Sales）只有两名员工，Henry 的工资最高，
Sam 的工资排第二。
*/``````

https://leetcode-cn.com
...全文
78 点赞 收藏 3

3 条回复

``````USE tempdb
GO
IF OBJECT_ID('dbo.[Employee]') IS NOT NULL
DROP TABLE dbo.[Employee]
GO
CREATE TABLE dbo.[Employee](
[Id] int
,[Name] NVARCHAR(10)
,[Salary] int
,[DepartmentId] int
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[Employee] VALUES(N'1',N'Joe',N'85000',N'1')
INSERT INTO dbo.[Employee] VALUES(N'2',N'Henry',N'80000',N'2')
INSERT INTO dbo.[Employee] VALUES(N'3',N'Sam',N'60000',N'2')
INSERT INTO dbo.[Employee] VALUES(N'4',N'Max',N'90000',N'1')
INSERT INTO dbo.[Employee] VALUES(N'5',N'Janet',N'69000',N'1')
INSERT INTO dbo.[Employee] VALUES(N'6',N'Randy',N'85000',N'1')
INSERT INTO dbo.[Employee] VALUES(N'7',N'Will',N'70000',N'1')
GO
IF OBJECT_ID('Department') IS NOT NULL
DROP TABLE Department
GO
CREATE TABLE Department(
Id INT,
[Name] NVARCHAR(10)
)
GO
SET NOCOUNT ON
INSERT INTO Department VALUES(1,'IT')
INSERT INTO Department VALUES(2,'Sales')
----------- 以上为测试表和测试数据 --------------

SELECT Department
,Employee
,Salary
FROM (
SELECT DENSE_RANK() OVER (PARTITION BY e.DepartmentId ORDER BY e.Salary DESC) AS rid
,d.Name AS Department
,e.Name AS Employee
,e.Salary
FROM Employee AS e INNER JOIN Department AS d
ON e.[DepartmentId]=d.Id
) AS t
WHERE t.rid<=3
ORDER BY Department,rid
/*
Department Employee   Salary
---------- ---------- -----------
IT         Max        90000
IT         Joe        85000
IT         Randy      85000
IT         Will       70000
Sales      Henry      80000
Sales      Sam        60000
*/
``````

9308

12.1w+

MS-SQL Server 疑难问题