最近刷了挺久的leetCode的sql题,总结了以下部分规律
可以解决LeetCode中的许多题目:
例如以下查询两个部门薪资的排名前二的薪水时多少
Employee 表
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
+----+-------+--------+--------------+
Department 表
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
查询结果
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| Sales | Henry | 80000 |
+------------+----------+--------+
SELECT
d.NAME AS 'Department',
e1.NAME AS 'Employee',
e1.Salary
FROM
Employee e1
JOIN Department d ON e1.DepartmentId = d.Id
WHERE
#比查询出来的Salary大的个数最多为2,则表示是前三
(
SELECT
COUNT( DISTINCT e2.Salary )
FROM
Employee e2
WHERE
e2.Salary > e1.Salary
AND e1.DepartmentId = e2.DepartmentId
) <3;
可以解决包括:
184题:部门薪资最高的员工(中等题)
185题. 部门工资前三高的所有员工(困难题)
我是“道祖且长”,一个在互联网苟且偷生的Java程序员