mysql实现求分组topn

2019-09-07

分组 TopN 问题

实现每个班级成绩前两名的学生信息

在mysql的最新版本mysql8已经支持了排名函数RANK，DENSE_RANK和ROW_NUMBER。但是在就得版本中还不支持这些函数，只能自己实现。实现方法主要用到了条件判断语句（CASE WHEN或IF）和添加临时变量。
区别RANK，DENSE_RANK和ROW_NUMBER
RANK并列跳跃排名，并列即相同的值，相同的值保留重复名次，遇到下一个不同值时，跳跃到总共的排名。
DENSE_RANK并列连续排序，并列即相同的值，相同的值保留重复名次，遇到下一个不同值时，依然按照连续数字排名。
ROW_NUMBER连续排名，即使相同的值，依旧按照连续数字进行排名。

这些函数再hive中都有实现 hive的窗口分析函数

建表语句

CREATE TABLE score_info (
  id bigint(18) NOT NULL AUTO_INCREMENT,
  name varchar(20) DEFAULT NULL,
  class_no varchar(10) DEFAULT NULL,
  score int(5) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;

插入数据

insert into score_info values('1',"张三"," 高三1班 "," 90")   ;
insert into score_info values('2',"李四"," 高三1班 "," 92")   ;
insert into score_info values('3',"王五"," 高三1班 "," 99")   ;
insert into score_info values('4',"赵流"," 高三1班 "," 93")   ;
insert into score_info values('5',"孙启"," 高三1班 "," 95")   ;
insert into score_info values('6',"古蔺"," 高三2班 "," 93")   ;
insert into score_info values('7',"孙雪"," 高三2班 "," 92")   ;
insert into score_info values('8',"万凯"," 高三2班 "," 88")   ;
insert into score_info values('9',"李润"," 高三2班 "," 98")   ;
insert into score_info values('10',"林旭"," 高三2班 ","100")   ;
insert into score_info values('11',"艾奇"," 高三2班 "," 98")   ;
insert into score_info values('12',"爱丽斯"," 高三3班 "," 97") ;
insert into score_info values('13',"詹姆斯"," 高三3班 "," 93") ;
insert into score_info values('14',"阿伦"," 高三3班 "," 91")   ;
insert into score_info values('15',"史提芬"," 高三3班 "," 99") ;
insert into score_info values('16',"克里斯"," 高三3班 "," 97") ;

实现

-- 实现方式_1
SELECT *
FROM score_info si
WHERE (
	SELECT COUNT(1) 
	FROM score_info 
	WHERE class_no = si.class_no AND score > si.score
) < 2
ORDER BY si.class_no, si.score DESC;

-- 实现方式_2
SELECT *
FROM score_info si
WHERE EXISTS (
	SELECT 1 
	FROM score_info 
	WHERE class_no = si.class_no AND score > si.score
	HAVING COUNT(1) < 2
)
ORDER BY si.class_no, si.score DESC;

-- 实现方式_3

select a.id,a.name,a.class_no, a.score 
from score_info a
left join score_info b
on a.class_no=b.class_no
and  a.score<b.score
group by a.id,a.name
having count(b.id)<2
ORDER BY a.class_no, a.score DESC;