MySQL日记1: 找到每个部门工资前三高的员工
员工信息表：Employee
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| id | int |
| name | varchar |
| salary | int |
| departmentId | int |
+--------------+---------+
Id是该表的主键列。
departmentId是Department表中ID的外键。
该表的每一行都表示员工的ID、姓名和工资。它还包含了他们部门的ID。
部门表: Department
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| name | varchar |
+-------------+---------+
Id是该表的主键列。
该表的每一行表示部门ID和部门名。
找到每个部门工资前三高的员工，输出部门名称、员工名称、员工薪资？
Step 1: 先找到各部门工资前三高的员工
(1) 因为工资可能会重复，无法用Limit 0, 3。
(2) count + distinct 可以去计算能比e1.salary高的工资水平的数量，如果小于三个，那e1.salary必然是前三。
SELECT
e1.Name AS Employee, e1.Salary AS Salary
FROM
Employee e1
WHERE
(
SELECT
COUNT(DISTINCT e2.Salary)
FROM
Employee e2
WHERE
e2.Salary > e1.Salary
) < 3
;
Step2：通过表连接找到部门名称
SELECT
d.Name AS Department, e1.Name AS Employee, e1.Salary AS Salary
FROM
Employee e1
LEFT JOIN
Department d
ON
e1.DepartmentId = d.Id
WHERE
(
SELECT
COUNT(DISTINCT e2.Salary)
FROM
Employee e2
WHERE
e2.Salary > e1.Salary AND e1.DepartmentId = e2.DepartmentId
) < 3
;