来源LeetCode
部门工资前三高的所有员工-被关联表
Employee
表包含所有员工信息,每个员工有其对应的工号 Id
,姓名 Name
,工资 Salary
和部门编号 DepartmentId
。
+----+-------+--------+--------------+
| 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 |
+----+----------+
编写一个 SQL 查询,找出每个部门获得前三高工资的所有员工。例如,根据上述给定的表,查询结果应返回:
IT 部门中,Max 获得了最高的工资,Randy 和 Joe 都拿到了第二高的工资,Will 的工资排第三。销售部门(Sales)只有两名员工,Henry 的工资最高,Sam 的工资排第二。
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 85000 |
| IT | Will | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+------------+----------+--------+
方法1-子查询
SELECT
d.Name AS 'Department',
e1.Name AS 'Employee',
e1.Salary
FROM
Employee e1
JOIN Department d ON e1.DepartmentId = d.Id
WHERE
3 > (SELECT
COUNT(DISTINCT e2.Salary)
FROM
Employee e2
WHERE
e2.Salary > e1.Salary
AND e1.DepartmentId = e2.DepartmentId
)
;
方法2-连接+group by + having
通过左自连接求出每个部门排名前3的薪水;判断雇员们的薪水是否在上面的薪水中;
// 写法1
select
d.name as department, e1.name as employee, e1.salary as salary
from
department d
join employee e1 on d.id = e1.departmentid
join employee e2 on e1.departmentid = e2.departmentid and e1.salary<=e2.salary
group by
d.name, e1.name
having
count(distinct e2.salary)<=3
order by
d.name, e1.salary desc
// 写法2
SELECT
t3.`Name` Department,
t1.`Name` Employee,
t2.Salary
FROM
Employee t1
INNER JOIN(
SELECT
e1.DepartmentId,
e1.Salary
FROM
Employee e1
LEFT JOIN Employee e2 ON e1.DepartmentId = e2.DepartmentId AND e1.Salary < e2.Salary
GROUP BY
e1.DepartmentId,
e1.Salary
HAVING
COUNT(DISTINCT e2.Salary) <= 2
) t2 ON t1.DepartmentId = t2.DepartmentId AND t1.Salary = t2.Salary
INNER JOIN Department t3 ON t1.DepartmentId = t3.Id
方法3-变量
1、对各个部门员工工资进行排序。核心思想是根据DepartmentId升序,Salary降序对Employee表进行排序,添加rank字段记录排名,组成临时表。rank字段规则:若上一条记录的DepartmentId与当前记录不同,则rank字段为1(不同部门);
若上一条记录的DepartmentId、Salary均与当前记录相同,则rank字段保持不变(排名相同);
若上一条记录的DepartmentId与当前记录相同,rank与当前记录不同,则SalaryRank字段+1。
2、取出排名中名次小于等于三的员工
3、与部门名称表进行连接
SELECT
d.NAME department, t.NAME employee, salary
FROM
( SELECT
*, @r := IF(@pD = departmentid, IF(@pS = salary, @r, @r + 1 ), 1 ) AS 'rank',
@pD := departmentid,
@pS := salary
FROM
employee, ( SELECT @pS := NULL, @pD := NULL, @r := 0 ) as init
ORDER BY
departmentid, salary DESC ) t
JOIN department d ON t.departmentid = d.id
WHERE
t.rank <=3
SELECT
max(ID) as risk_warning_id
, related_guarantee_id
FROM cfbiz_risk_warning
-- ifnull 是为了历史数据
where
delete_flag = 0 and
ifnull(risk_warning_confirm_date, CREATED_TIME) <= curdate()
GROUP BY related_guarantee_id
方法4-limit
SELECT
d.Name as Department,
e.Name as Employee,
e.Salary as Salary
FROM
Employee as e LEFT JOIN (
SELECT
*, IFNULL((
SELECT DISTINCT e1.Salary
FROM Employee as e1
WHERE e1.DepartmentId = Department.Id
ORDER BY e1.Salary DESC
LIMIT 2,1 -- 偏移,条数
),0) as TopSalary
FROM Department
) as d ON e.DepartmentId = d.Id
WHERE
e.Salary >= d.TopSalary
方法5-窗口函数
首先通过窗口函数dense_rank实现每个部门内部的工资排序,然后将员工的id和自己所在部门工资的名词进行绑定生成表c,然后三个表连接是,将排名是1,2,3的行选出来即可。
select
d.Name as Department,
e2.Name as Employee,
e2.Salary
from
Department d inner join
(
select e.*,
dense_rank() over(partition by DepartmentID
Order by Salary DESC) as 'rank'
from Employee e
) e2 on d.Id= e2.DepartmentID
where e2.rank<=3
order by Department AND Salary
如果还有其他方法,不吝赐教