--下面我演示下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;
【【MySQL利用变量实现各种排序】】--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 |
+------+------+-------+
