mysql实现dense_rank_mysql模拟rank, dense_rank

http://stackoverflow.com/questions/532878/how-to-perform-grouped-ranking-in-mysql

CREATE TABLE `test` (

`student_id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`subject` varchar(10) CHARACTER SET latin1 DEFAULT NULL,

`score` tinyint(4) DEFAULT NULL,

PRIMARY KEY (`student_id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

insert into `test`(`id`,`student_id`,`subject_name`,`score`) values (1,1,'yw',80);

insert into `test`(`id`,`student_id`,`subject_name`,`score`) values (2,1,'sx',90);

insert into `test`(`id`,`student_id`,`subject_name`,`score`) values (3,2,'yw',85);

insert into `test`(`id`,`student_id`,`subject_name`,`score`) values (4,2,'sx',95);

insert into `test`(`id`,`student_id`,`subject_name`,`score`) values (5,3,'yw',70);

insert into `test`(`id`,`student_id`,`subject_name`,`score`) values (6,3,'sx',100);

insert into `test`(`id`,`student_id`,`subject_name`,`score`) values (7,4,'yw',70);

insert into `test`(`id`,`student_id`,`subject_name`,`score`) values (8,4,'sx',70);

subject_name: 科目 yw:语文 sx:数学

dense_rank

select subject_name as sbn , student_id as sid, score,

@rank:=case when @sn <> subject_name then 1 when @score <> score then @rank+1 else @rank+0 end as rank,

@sn := subject_name as sn,

@score := score as score

from `test` p,

(select @rank := 0) r,

(select @sn := '') u,

(select @score := -1) i

order by subject_name, score;

result:

sbnsidscoreranksnscore

sx4701sx70

sx1902sx90

sx2953sx95

sx31004sx100

yw3701yw70

yw4701yw70

yw1802yw80

yw2853yw85

rank

select subject_name as sbn, student_id as sid, score,

@rank:=case when @sn <> subject_name then 1 when @score <> score then @rank+@range else @rank+0 end as rank,

@range:=case when @sn <> subject_name then 1 when @score = score then @range+1 else 1 end as ran ,

@sn := subject_name as sn, @score := score as sc

from `test` p, (select @rank := 0) r,

(select @sn := '') u, (select @score := -1) i, (select @range := 1) g

order by subject_name, score;

sbnsidscorerankransnsc

sx47011sx70

sx19021sx90

sx29531sx95

sx310041sx100

yw37011yw70

yw47012yw70

yw18031yw80

yw28541yw85

分享到：

2012-06-14 14:52

浏览 988

分类:数据库

评论