MySQL如何使用变量实现各种排序

这篇文章主要介绍了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 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如何使用变量实现各种排序内容对大家有帮助，同时也希望大家多多支持，关注行业资讯频道，遇到问题就找，详细的解决方法等着你来学习!