mysql分组排序

来源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

如果还有其他方法，不吝赐教