This article mainly introduces MySQLUsing variables to achieve various sorting. Friends in need can refer to the
core code
--下面我演示下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 | +------+------+-------+
The above is the detailed content of MySQL sample code analysis on using variables to implement various sorting. For more information, please follow other related articles on the PHP Chinese website!