mysql 分组取前n条

u010009709

已于 2023-05-09 20:15:14 修改

阅读量133

点赞数

文章标签： mysql 数据库 sql

于 2023-05-09 19:26:59 首次发布

本文链接：https://blog.csdn.net/u010009709/article/details/130586992

版权

一、推荐方式

-- 根据工资 salary 由高到低排序，取每个部门top 2工资员工（不关注工资相同情况）

select * from (
	select 
	t1.*,
	@rn_no := if(t1.department_id = @before_dept_no, @rn_no + 1, 1)  as rn_no,
	@before_dept_no :=  t1.department_id as before_dept_no
	from employee t1 join (select @before_dept_no := null, @before_salary := null, @rn_no :=0 ) t2
	order by t1.department_id, t1.salary desc
) t0  where rn_no <= 2

-- 根据工资 salary 由高到低排序，取每个部门top 2工资员工（关注工资相同情况）

select * from (
    select 
    t1.*,
    @rn_no := if(t1.department_id = @before_dept_no, if(@before_salary = t1.salary, @rn_no ,@rn_no + 1), 1)  as rn_no,
    @before_dept_no :=  t1.department_id as before_dept_no,
    @before_salary := t1.salary as before_salary
    from employee t1 join (select @before_dept_no := null, @before_salary := null, @rn_no :=0 ) t2
    order by t1.department_id, t1.salary desc
) t0  where rn_no <= 2

二、可选的方式（mysql版本>8）窗口函数：

select * from (
    select * , ROW_NUMBER() OVER (PARTITION BY department_id order by salary desc) as rn_no
    from employee 
) t1     where t1.rn_no <= 2

三、不推荐方式：

select t1.* from employee t1  
where 2 > (
    select count(*) from employee t2  where t1.department_id = t2.department_id and t1.salary < t2.salary)
order by t1.department_id, t1.salary desc

explain: DEPENDENT SUBQUERY 子查询执行次数依赖主查询数量效率太低。