mysql模拟rank, dense_rank-优快云博客

本文链接：https://blog.youkuaiyun.com/iteye_15484/article/details/82342384

本文详细介绍MySQL中的排名函数dense_rank和rank的实际应用。通过具体实例展示了如何使用这些函数为不同学生在不同科目上的成绩进行排名，并对比了两种排名方法的区别。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

参考[url]http://stackoverflow.com/questions/532878/how-to-perform-grouped-ranking-in-mysql[/url]


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:数学
[size=large]dense_rank[/size]


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:



sbn	sid	score	rank	sn	score
sx	4	70	1	sx	70
sx	1	90	2	sx	90
sx	2	95	3	sx	95
sx	3	100	4	sx	100
yw	3	70	1	yw	70
yw	4	70	1	yw	70
yw	1	80	2	yw	80
yw	2	85	3	yw	85

[size=large]
rank[/size]


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;


sbn	sid	score	rank	ran	sn	sc
sx	4	70	1	1	sx	70
sx	1	90	2	1	sx	90
sx	2	95	3	1	sx	95
sx	3	100	4	1	sx	100
yw	3	70	1	1	yw	70
yw	4	70	1	2	yw	70
yw	1	80	3	1	yw	80
yw	2	85	4	1	yw	85