MySQL排序 输出序号

 方式一：窗口函数（MySQL8.0以上版本）

        1.忽略重复值，序号连续 row_number() over(order by column desc/asc)

select user_nm, subject, score, row_number() over(order by score ) xh from result;

        2.重复值序号相同，整体序号不连续 rank()

select user_nm, subject, score, rank() over(order by score ) xh from result;

       

        3.重复值序号相同，序号连续  dense_rank() 

select user_nm, subject, score, dense_rank() over(order by score ) xh from result;

方式二：使用变量

        1.忽略重复值，序号连续

set @rank = 0;
select user_nm, subject, score, @rank := @rank +1 xh from result;

select user_nm, subject, score, @i := @i +1 xh 
from result , (select @i := 0 )it ;

        2.重复值序号相同，序号不连续

--------------------------------------------------------------

        3.重复值序号相同，序号连续        

set @rank = 0, @prev = null;
select user_nm, subject, score, 
	case when @prev = score then @rank
	 when @prev := score then @rank := @rank +1 end xh from result;