mysql 分组取前10条数据

mysql 分组取前10条数据

1.使用自定义函数

select 
	name,gender,age,rankk 
from
(select
	name,
	gender,
	age,
	@rankk:=if(@gen=gender,@rankk+1,1) as rankk,
	@gen:=gender
from t_user,(select @rankk:=0,@gen:=null) temp   
order by gender,age asc) a where a.rankk < 10;

2.使用row_number() over() 函数

select 
	name,gender,age,rankk 
from
(select
    row_number() over (partition by gender order by gender,age asc) as  rn,
	gender,
	age
 from t_user  ) a where a.rankk < 10;