MySQL使用變量完成各種排序詳細(xì)說(shuō)明

核心代碼

--下面我演示下MySQL中的排序列的實(shí)現(xiàn)
--測(cè)試數(shù)據(jù)
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  
+------+------+-------+

相關(guān)推薦：

MYSQL創(chuàng)建函數(shù)出錯(cuò)如何解決

關(guān)于MySQL中REGEXP正則表達(dá)式使用小結(jié)

MySQL中的常用函數(shù)詳解

以上就是MySQL使用變量實(shí)現(xiàn)各種排序詳解的詳細(xì)內(nèi)容，更多請(qǐng)關(guān)注php中文網(wǎng)其它相關(guān)文章！