mysql 跳跃查询_在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;