mysql设置变量用来排序_MySQL使用变量实现各种排序详解

本文主要介绍了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 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 |

+------+------+-------+

相关推荐：