本文主要介紹了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 | +------+------+-------+
相關推薦:
以上是MySQL使用變數實作各種排序詳解的詳細內容。更多資訊請關注PHP中文網其他相關文章!