核心代码
--下面我演示下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关于使用变量实现各种排序的示例代码分析的详细内容。