学习链接

mysql 实现row_number() over(partition by ) 分组排序功能
SQL基础系列（八）——排序、分组排序（RANK） - 比较全，不过须mysql8.0
Sql排名和分组排名

数据库的排序（mysql用户变量实现，比较全）
Mysql将查询后的数据进行排名的SQL语句
sql查询各科成绩前三名----详述过程，思路清晰不烧脑 这个很清楚，收藏量极高

MySQL 查询各科前三名（考虑成绩并列情况）

mysql 实现row_number() over(partition by ) 分组排序功能

MySQL5.7版本没有row_number()over()函数，MySQL8以上版本才有

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 -- MySQL5.7版本没有row_number()over()函数

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

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

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

select id,class,score,rank from (
	select 
		a.*,
		@rownum := @rownum +1, -- 注意: 这里的执行顺序晚于order by,(如果有having，则他们的顺序是: where < having < order by < select, 这个可以在这个例子中得到验证 )
		if( 
			@class = a.class or (@class is null and a.class is null), 
													/*相当于是整个查询执行完成后,然后执行select部分,一行一行处理,因为前面已经按了2个字段排序了,
													 所以第一个字段就已经达到分组效果了,所以第一个字段相同的数据都会集中在一起,所以在处理时,
													 就拿上一个的class值跟当前行作比较,如果相等,则说明是同一组的,然后直接就rank+1了,
													 如果不相等,则说明遇到了一个新的分组,则rank继续从1开始*/
			@rank := @rank + 1,
			@rank := 1
		) as rank,
		@class := a.class -- 记录当前行的class, 以便于处理下一行时,判断是否处于同一个组
	from 
		a,
		(select @rownum:=0,@class := null, @rank:=0 ) b  -- rownum用来记录行号, class用来记录上个班级(即相当于排序的第一个字段-分组效果),且初始值为null, rank从0开始
		
		order by a.class asc,a.score desc  -- 这里必须排好序,且必须按2个字段去排序，第一个字段作为排序字段达到分组的效果
) result
having rank < 2;

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

select 
	*   
from 
	a j  
where 
	(select 
			count(*) 
	 from 
			a k 
   where 
			k.class = j.class 
		and k.score > j.score   -- 为了方便理解，所以加上了j、k。
	) < 1                       -- 可以理解为：拿到a表的每一行,然后把这一行作为条件带入到where里面,然后作比较,满足条件的会留下来
order by class,score desc;

数据库的排序

一.mysql数据库排序：

1. 不管数据相同与否，排名依次排序（1,2,3,4,5,6,7…）

SELECT
 w.* ,
 (@rownum := @rownum + 1) AS rank1
FROM `work` w,(SELECT @rownum := 0) r
ORDER BY salary ;

需要一个rownum来实现排序，用select语句中给变量rownum赋初始值，每次执行+1；

2. 只要数据有相同的排名就一样，排名依次排序（1,2,2,3,3,4,5…）

SELECT
 w.* ,
(CASE 
  WHEN @rowtotal = w.`salary` THEN @rownum
  WHEN @rowtotal := w.`salary` THEN @rownum := @rownum+1
  WHEN @rowtotal = 0 THEN @rownum :=@rownum + 1 
END) rank2
FROM `work` w,(SELECT @rownum := 0,@rowtotal := NULL) r
ORDER BY salary DESC;

用rownum实现排序，rowtotal做判断和记录，开始为0 的时候，rownum+1，rowtotal的数据与当前行数据相等，rownum值不变，不相等时，赋新值给rowtotal并且rownum+1；

3. 只要数据有相同的排名就一样，但是相同排名也占位，排名依次排序（1,2,2,4,5,5,7…）

SELECT t.eid, t.did, t.salary, t.rank FROM
(SELECT
   w.* ,
   @rownum := IF(@rowtotal = w.`salary`, @rownum, @incrnum) AS rank, 
   @incrnum := @incrnum + 1, 
   @rowtotal := w.`salary`
 FROM `work` w, (SELECT @rownum := 0 ,@rowtotal := NULL ,@incrnum := 1) r
 ORDER BY salary DESC
) t;

需要三个参数，rownum用作排序，rowtotal记录当前值和判断，incrnum记录数值条数（自然排序），rowtotal与当前行的值相等，返回当前的rownum值，不相等就返回自然排序的值。

二.oracle的排序

(1)rank函数返回一个唯一的值，除非遇到相同的数据时，此时所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。

(2)dense_rank函数返回一个唯一的值，除非当碰到相同数据时，此时所有相同数据的排名都是一样的。

(3)row_number函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。