Mysql 增加序号规则
1:按照顺序增加序号 ,防止序号出现小数点,增加强转类型限制。
select cast(@currank:=@currank+1 as char) as rank_number,s.* from stuscore s,(select @currank:=0)t where s.subject='数学' order by score
rank: 代表序号 11335 跳过重复数字加一;rank函数用于返回结果集的分区内每行的排名,行的排名是相关行之前的排名数加一。
dense_rank: 不在跳过,再上一个序号加一 ; dense_rank函数出现相同排名时,将不跳过相同排名号
row_number: 123456 依次排序;row_number会为查询出来的每一行记录生成一个序号,依次顺序排序且不会重复,注意使用row_number函数时必须要用over子句选择对某一列进行排序才能生成序号。
partition by:分组排序
SELECT id, score
, rank() OVER (ORDER BY score DESC) AS 'rank'
, dense_rank() OVER (ORDER BY score DESC) AS 'dense_rank'
, row_number() OVER (ORDER BY id DESC) AS 'row_number'
, row_number() OVER (partition by score ORDER BY id DESC) AS 'row_number1'
FROM scores
order by id