Mariadb/mysql常用的sql排序

建表及添加数据数据

create table scores
(   id int(6)
   ,score DOUBLE(4,2)
);
insert into scores values(1,3.50);
insert into scores values(2,3.65);
insert into scores values(3,4.00);
insert into scores values(4,3.85);
insert into scores values(5,4.00);
insert into scores values(6,3.65);

顺序排名

SELECT
	scores.score, @rank := @rank + 1 AS rank
FROM
	(SELECT @rank := 0) AS b,
	scores 
ORDER BY score DESC;

4.00	1
4.00	2
3.85	3
3.65	4
3.65	5
3.50	6

并列排名

SELECT
	scores.score,
	CASE
		-- 当成绩相同时返回同样的排名
		WHEN @num = scores.score THEN
		-- 顺序排名的话 这里@rank:=@rank+1
		@rank
		-- 给@num赋值(赋值在when中肯定是true)且@rank+1
		WHEN @num := scores.score THEN
		@rank := @rank + 1 
	END AS RANK 
	FROM-- 定义变量  定义变量一般写在from语句之后,相当于在末尾定义变量
	( SELECT @rank := 0, @num = NULL ) AS b,
	scores 
ORDER BY
	score DESC;

4.00	1
4.00	1
3.85	2
3.65	3
3.65	3
3.50	4

排序函数（mysql8+支持）

升级数据库版本

查看安装的版本
[root@iZwz9fo7ndsemde4ip7c36Z ~]# rpm -qa|grep mariadb
mariadb-libs-5.5.68-1.el7.x86_64
mariadb-server-5.5.68-1.el7.x86_64
mariadb-5.5.68-1.el7.x86_64
[root@iZwz9fo7ndsemde4ip7c36Z ~]# mysql --version
mysql  Ver 15.1 Distrib 5.5.68-MariaDB, for Linux (x86_64) using readline 5.1
备份数据
[root@iZwz9fo7ndsemde4ip7c36Z ~]# mysqldump -u root -p --all-databases > dbbat.sql
Enter password:
[root@iZwz9fo7ndsemde4ip7c36Z ~]# ls
dbbat.sql  dist  dnsdao  dnsdao.org  images  logs  root  root_bak  www  zips
创建mysql版本yum源（/etc/yum.repo.d/）
[mariadb]
name = MariaDB
baseurl = https://tw1.mirror.blendbyte.net/mariadb/yum/10.5/centos7-amd64
gpgkey=https://tw1.mirror.blendbyte.net/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1
清yum源并缓存yum源
yum clean all && yum makecache
安装
yum install mariadb mariadb-server -y

顺序排名

SELECT
	score,
	--  over()是必须要写的(区分开窗函数和聚合函数),开窗函数与分组聚合函数比较相似，都是通过指定字段将数据分成多份
	row_number() over(ORDER BY score DESC) AS rank
FROM
	scores;
	
4.00	1
4.00	2
3.85	3
3.65	4
3.65	5
3.50	6

并列排名（跳跃排名 不连续）

SELECT score,rank() over(ORDER BY score desc) AS rank
from scores

4.00	1
4.00	1
3.85	3
3.65	4
3.65	4
3.50	6

并列排名

SELECT score,dense_rank() over(ORDER BY score desc) AS rank 
from scores 

4.00	1
4.00	1
3.85	2
3.65	3
3.65	3
3.50	4

删除重复的成绩 只留id最小的一条

DELETE 
FROM
	scores 
WHERE
	id NOT IN (
	SELECT
		MIN( id ) 
	FROM
		scores 
	GROUP BY
	score)