学习链接
mysql 实现row_number() over(partition by ) 分组排序功能
SQL基础系列(八)——排序、分组排序(RANK) - 比较全,不过须mysql8.0
Sql排名和分组排名
数据库的排序(mysql用户变量实现,比较全)
Mysql将查询后的数据进行排名的SQL语句
sql查询各科成绩前三名----详述过程,思路清晰不烧脑 这个很清楚,收藏量极高
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函数返回一个唯一的值,当碰到相同数据时,排名按照记录集中记录的顺序依次递增。