LEETCODE数据库:185.DepartmentTopThreeSalaries

发表于 更新于 分类于 Waline： 阅读次数：

题目

The Employee table holds all employees. Every employee has an Id, and there is also a column for the department Id.

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

The Department table holds all departments of the company.

Id Name
1 IT
2 Sales

Write a SQL query to find employees who earn the top three salaries in each of the department. For the above tables, your SQL query should return the following rows (order of rows does not matter).

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

Explanation:

In IT department, Max earns the highest salary, both Randy and Joe earn the second highest salary, and Will earns the third highest salary. There are only two employees in the Sales department, Henry earns the highest salary while Sam earns the second highest salary.

题解

1
2
3
4
5
6
7
8
# Write your MySQL query statement below
SELECT s2.Name Department,s1.Name Employee,s1.Salary
FROM Employee s1,Department s2
WHERE s1.DepartmentId=s2.Id AND 3>(
    SELECT COUNT(DISTINCT s3.Salary)
    FROM Employee s3
    WHERE s1.Salary<s3.Salary AND s1.DepartmentId=s3.DepartmentId
);

说明

  • WHERE后半部分其实是查找比当前人员工资高的人员有一类，如果为2类，则当前人员就是工资第三高的一批人，以此类推。