MySQL使用变量实现各种排序
16lz
2021-12-31
核心代码
--下面我演示下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 SELECT1;--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 scoreFROM 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 |+------+------+-------+
更多相关文章
- MySQL系列多表连接查询92及99语法示例详解教程
- Linux下MYSQL 5.7 找回root密码的问题(亲测可用)
- MySQL 什么时候使用INNER JOIN 或 LEFT JOIN
- ES6 变量声明,箭头函数,数组方法,解构赋值,JSON,类与继承,模块化练习
- [Android(安卓)NDK]Android(安卓)JNI开发例子 ---3 在JNI中实现o
- Android之adb环境变量配置
- android 目录结构,adb环境变量配置
- 链接器解析多重定义的全局变量
- android从服务器下载文件(php+apache+win7+MySql)