Mysql 取某个字段分组的每组前n条数据

Mysql 取某个字段分组的每组前n条数据

数据准备

create table employee (empid int ,deptid int ,salary decimal(10,2))
insert into employee values(1,10,5500.00)
insert into employee values(2,10,4500.00)
insert into employee values(3,20,1900.00)
insert into employee values(3,20,1900.00)
insert into employee values(4,20,4800.00)
insert into employee values(5,40,6500.00)
insert into employee values(6,40,14500.00)
insert into employee values(7,40,44500.00)
insert into employee values(8,50,6500.00)
insert into employee values(9,50,7500.00)

方法1，并包含同排行的数据

select *
from employee e1
where 2 > (select count(*)
	from employee e2
	where e2.deptid = e1.deptid and
	e2.salary > e1.salary)

可以把where看成for循环。e2是内循环，e1是外循环。解释：查询同部门下比当前薪水大的记录数小于两条的数据——即就是查找薪水排行前2的记录。但是存在相同值时，都会展示。如果要求唯一可以参考下面的sql

方法2，不包含相同排行的数据（可以按要求排序）

select e.salary ,e.deptid , e.empid 
from (SELECT *, Row_Number() OVER (partition by deptid ORDER BY salary desc) as num
		from employee) e 
where 2 > (select count(*)
	from (SELECT *, Row_Number() OVER (partition by deptid ORDER BY salary desc) as num
		from employee) e2
	where e2.deptid = e.deptid and
	e2.num < e.num)

原理和上面的相同，只是取的是 Row_Number() OVER (partition by deptid ORDER BY salary desc) 查询的子表

row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)