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;
--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关于使用变量实现各种排序的示例代码分析的详细内容，更多请关注php中文网其它相关文章！

……