在mysql中进行统计排序(跳跃排序和连续排序)

-- 在mysql中进行统计排序(跳跃排序和连续排序)
CREATE TABLE `tb_score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) 

select * from tb_score;

delete from tb_score where id > 0;
commit;

insert into tb_score (score) values(43),(22),(22),(18);
commit;

-- 跳跃排序,有并列名次的情况下,整个排序序号不连续
-- 将原表添加一列自增序号的列，然后按照数字进行分组，取得最小的自增序号，在最外层按照各自的最小序号排序
select z.*, x.min_cnt from tb_score z,
(
select score, min(cnt) min_cnt from (
(select id, score,  (@i:=@i+1) cnt from tb_score, (SELECT @i:=0) as i  order by score ) ) a
group by score ) x
where z.score = x.score
order by x.min_cnt;

-- 连续排序,有并列名次的情况下,整个排序序号是连续
-- 将原表对数字进行分组并排序，然后将其做为一个子表，在上面添加一个自增的序号
select * from tb_score t , (
select tmp.score,  (@i:=@i+1) rank from (
select score  from tb_score  
group by score order by score asc ) tmp,(SELECT @i:=0) as i ) b
where t.score = b.score
order by b.rank;