mysql解决分组TOP N问题，8.0以下实现类似row_number()分组排名

建表语句

CREATE TABLE `tb_score` (
  `id` BIGINT(18) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(20) DEFAULT NULL,
  `class` VARCHAR(10) DEFAULT NULL,
  `score` INT(5) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;

INSERT INTO tb_score VALUES(1, "张三", "A", 94);
INSERT INTO tb_score VALUES(2, "李四", "B", 92);
INSERT INTO tb_score VALUES(3, "王五", "C", 83);
INSERT INTO tb_score VALUES(4, "赵六", "A", 65);
INSERT INTO tb_score VALUES(5, "曹操", "B", 69);
INSERT INTO tb_score VALUES(6, "孙权", "C", 85);
INSERT INTO tb_score VALUES(7, "刘备", "A", 80);
INSERT INTO tb_score VALUES(8, "宋江", "B", 94);
INSERT INTO tb_score VALUES(9, "李广", "C", 96);
INSERT INTO tb_score VALUES(10, "张飞", "B", 60);

分组TOP N

查询每个班级分数最高前两名
1.关联子查询

select * from tb_score a where 
(
select count(*) from tb_score b 
where a.class=b.class and a.score<b.score
)<2
ORDER BY class

2.join查询

select tb1.* from tb_score tb1 
left join tb_score tb2
on tb1.class=tb2.class
and tb1.score<tb2.score
group by id,class
having count(1)<2
ORDER BY class

8.0以下实现类似row_number窗口函数，添加分组排名

select id,name,class,score,
	CASE
				@temp_class 
				WHEN class THEN
				@rk := @rk + 1 ELSE @rk := 1 
		END rank,
    @temp_class := class
from tb_score,
( SELECT @temp_class := '', @rk := 0 ) b 
ORDER BY class,score DESC

类似8.0以上的 row_number() over (partition by class order by score )，可以解决top n问题和类型连续登录天数问题.（可以使用日期减去排名 as temp ，然后count(temp)>天数）

其他自关联查询

1.查询每个班级高于平均分的信息

select * from tb_score a where score>
(
select avg(score) from tb_score b where a.class=b.class
)
ORDER BY class