博客

其他相关的文章收集：

SQL数据库语法及题目汇总

问题描述：

SQL经典题目之一：求部门工资前三高的所有员工。

题目内容包含两个表格Employee和Department 。

#Employee
+----+-------+--------+--------------+
| 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    |
+----+----------+

答案整理：

解法一：使用窗口函数

解题思路：

1） 因为Employee中有DepartmentId，可以直接根据部门ID分组，给员工工资排序，选择前三员工信息；

2）Join连接Employee表和Department表，得出部门名称。

复习窗口函数知识点链接

SELECT 
B.Name AS Department,
A.Name AS Employee,
A.Salary
FROM (SELECT DENSE_RANK() OVER (partition by DepartmentId order by Salary desc) AS ranking,DepartmentId,Name,Salary
      FROM Employee) AS A
JOIN Department AS B ON A.DepartmentId=B.id
WHERE A.ranking<=3

#结果输出：
IT	Joe	85000.00
IT	Randy	85000.00
IT	Will	70000.00
IT	Max	69000.00
IT	Janet	69000.00
Sales	Henry	80000.00
Sales	Same	60000.00

解法二：使用 JOIN 和Where

解题思路：公司里前 3 高的薪水意味着有不超过 3 个工资比这些值大。

SELECT
    d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
FROM
    Employee e1
        JOIN
    Department d ON e1.DepartmentId = d.Id
WHERE
#工资级别数量小于等于3，即最多只有3个工资级别，也就是前三高
    3 > (SELECT
            COUNT(DISTINCT e2.Salary)
        FROM
            Employee e2
        WHERE
        #e2的工资级别大于等于e1的工资级别
            e2.Salary > e1.Salary
                AND e1.DepartmentId = e2.DepartmentId
        )
;

解法三：使用 JOIN 和Having

SELECT
	d.NAME AS Department,
	e1.NAME AS Employee,
	e1.salary AS Salary 
FROM
	employee AS e1
	LEFT JOIN employee AS e2 ON e1.DepartmentId = e2.DepartmentId 
	AND e1.Salary < e2.Salary
	LEFT JOIN department d ON e1.DepartmentId = d.id 
GROUP BY
	e1.Id 
HAVING
	count( DISTINCT e2.Salary ) <= 2

来源：力扣（LeetCode）

链接：https://leetcode-cn.com/problems/department-top-three-salaries