引用

1、mysql分组取每组前几条记录(排序)
2、mysql 实现row_number() over(partition by ) 分组排序功能
3、mysql 中关于获取行号@rownum：=@rownum+1

创建表和打入数据

--创建表
CREATE TABLE `a` (
	`ID` INT(10) NULL DEFAULT NULL,
	`class` INT(10) NULL DEFAULT NULL,
	`score` INT(10) NULL DEFAULT NULL
)
COLLATE='utf8_general_ci';
--插入数据
insert into a values (1,1,90);
insert into a values (2,1,70);
insert into a values (3,1,90);
insert into a values (4,1,80);
insert into a values (5,2,100);
insert into a values (6,2,80);
insert into a values (7,2,110);
insert into a values (8,2,80);
insert into a values (9,2,80);
insert into a values (10,2,60);

实现1：使用开窗函数，兼容oracle

select * from (
  select *,row_number() over(partition by class order by score desc) mm from  a
) b where mm=1

如果提示：Every derived table must have its own alias错误，则表示需要对查询的结果加个别名 b

实现2：自定义实现row_number() over(partition by ) 分组排序功能

1、获取每个班级最高得分（不包括并列）

select id,class,score,rank from (
	select 
		b.*,
		@rownum := @rownum+1 ,-- 定义用户变量@rownum来记录数据的行号。通过赋值语句@rownum := @rownum+1来累加达到递增行号。
		if(@pdept=b.class,@rank:=@rank+1,@rank:=1) as rank,-- 如果当前分组编号和上一次分组编号相同，则@rank（对每一组的数据进行编号）值加1，否则表示为新的分组，从1开始
		@pdept:=b.class -- 定义变量@pdept用来保存上一次的分组id
	from (select * from a order by a.class,a.score desc) b ,-- 这里的排序不确定是否需要，保险点还是加上吧
		(select @rownum :=0 , @pdept := null ,@rank:=0) c  -- 初始化自定义变量值
	order by b.class,b.score desc -- 该排序必须，否则结果会不对
) result
having rank < 2;

2、获取每个班级最高得分（包括并列）

select *   
from a   
where (select count(*) 
from a b 
where b.class = a.class and b.score > a.score  ) < 1 
order by class,score desc;