MySQL使用变量实现各种排序
作者:feixianxxx 时间:2024-01-22 10:36:14
核心代码
--下面我演示下MySQL中的排序列的实现
--测试数据
CREATE TABLE tb
(
score INT
);
INSERT tb SELECT
5 UNION ALL SELECT
4 UNION ALL SELECT
4 UNION ALL SELECT
4 UNION ALL SELECT
3 UNION ALL SELECT
2 UNION ALL SELECT
1;
--1.row_number式的排序
SET @row_number =0;
SELECT @row_number := @row_number+1 AS row_number,score
FROM tb
ORDER BY score DESC ;
+------------+-------+
| row_number | score |
+------------+-------+
| 1 | 5 |
| 2 | 4 |
| 3 | 4 |
| 4 | 4 |
| 5 | 3 |
| 6 | 2 |
| 7 | 1 |
+------------+-------+
--2.dense_rank式的排序
SET @dense_rank = 0,@prev_score = NULL;
SELECT @dense_rank :=IF(@prev_score=score,@dense_rank,@dense_rank+1) AS decnse_rank,
@prev_score := score AS score
FROM tb
ORDER BY score DESC ;
+-------------+-------+
| decnse_rank | score |
+-------------+-------+
| 1 | 5 |
| 2 | 4 |
| 2 | 4 |
| 2 | 4 |
| 3 | 3 |
| 4 | 2 |
| 5 | 1 |
+-------------+-------+
--3.rank式的排序
SET @row=0,@rank=0,@prev_score=NULL;
SELECT @row:=@row+1 AS ROW,
@rank:=IF(@prev_score=score,@rank,@row) AS rank,
@prev_score:=score AS score
FROM tb
ORDER BY score DESC;
+------+------+-------+
| ROW | rank | score |
+------+------+-------+
| 1 | 1 | 5 |
| 2 | 2 | 4 |
| 3 | 2 | 4 |
| 4 | 2 | 4 |
| 5 | 5 | 3 |
| 6 | 6 | 2 |
| 7 | 7 | 1 |
+------+------+-------+
来源:http://blog.csdn.net/feixianxxx/article/details/5807973
标签:MySQL,变量,各种排序
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
python中os和sys模块的区别与常用方法总结
2022-05-26 18:04:24
Python3获取cookie常用三种方案
2022-10-20 02:56:19
Python3 filecmp模块测试比较文件原理解析
2021-10-28 15:24:19
![](https://img.aspxhome.com/file/2023/5/99625_0s.png)
python PaddleOCR库用法及知识点详解
2023-02-04 16:01:03
mysql主从复制的实现步骤
2024-01-18 02:50:25
![](https://img.aspxhome.com/file/2023/5/108865_0s.png)
黑客谈 MSSQL SA权限入侵的感悟
2008-03-20 10:18:00
python pickle存储、读取大数据量列表、字典数据的方法
2021-10-01 11:22:51
非集成环境的php运行环境(Apache配置、Mysql)搭建安装图文教程
2023-07-21 16:21:41
![](https://img.aspxhome.com/file/2023/0/5707955470_0s.png)
解决Alexnet训练模型在每个epoch中准确率和loss都会一升一降问题
2022-12-06 16:17:37
![](https://img.aspxhome.com/file/2023/5/107785_0s.jpg)
Pandas 类型转换astype()的实现
2022-03-13 17:39:52
![](https://img.aspxhome.com/file/2023/1/64331_0s.jpg)
非常酷炫的Bootstrap图片轮播动画
2024-04-23 09:16:34
![](https://img.aspxhome.com/file/2023/4/135594_0s.jpg)
SQL Server AlwaysOn读写分离配置图文教程
2024-01-19 10:20:20
![](https://img.aspxhome.com/file/2023/6/124856_0s.png)
使用python进行波形及频谱绘制的方法
2023-02-07 02:48:58
vue获取data数据改变前后的值方法
2024-04-30 10:35:10
Python制作运行进度条的实现效果(代码运行不无聊)
2021-04-11 15:46:10
![](https://img.aspxhome.com/file/2023/6/105196_0s.gif)
python GUI库图形界面开发之PyQt5滚动条控件QScrollBar详细使用方法与实例
2021-04-26 09:11:26
![](https://img.aspxhome.com/file/2023/0/92500_0s.png)
MySQL的常用命令集锦
2024-01-16 00:40:44
SQL Server 作业同步 (结合备份作业)
2012-07-11 15:59:47
JavaScript通过改变文字透明度实现的文字闪烁效果实例
2024-04-16 09:13:28
![](https://img.aspxhome.com/file/2023/5/136665_0s.gif)
python有证书的加密解密实现方法
2023-02-10 08:07:30