共计 1149 个字符，预计需要花费 3 分钟才能阅读完成。
自动写代码机器人，免费开通
丸趣 TV 小编给大家分享一下 MySQL 使用变量如何实现各种排序，希望大家阅读完这篇文章后大所收获，下面让我们一起去探讨吧！
-- 下面我演示下 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.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 |
+------+------+-------+看完了这篇文章，相信你对 MySQL 使用变量如何实现各种排序有了一定的了解，想了解更多相关知识，欢迎关注丸趣 TV 行业资讯频道，感谢各位的阅读！
向 AI 问一下细节
丸趣 TV 网 – 提供最优质的资源集合！
正文完
