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排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)