mysql group top_Mysql group by 后取 top N 问题

测试数据

DROP TABLE IF EXISTS `t`;

CREATE TABLE `t` (

`id` int(11) NOT NULL,

`class` varchar(255) DEFAULT NULL,

`score` int(255) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `t` VALUES ('1', '1', '55');

INSERT INTO `t` VALUES ('2', '1', '67');

INSERT INTO `t` VALUES ('3', '2', '77');

INSERT INTO `t` VALUES ('4', '2', '87');

INSERT INTO `t` VALUES ('5', '1', '34');

INSERT INTO `t` VALUES ('6', '2', '90');

INSERT INTO `t` VALUES ('7', '2', '10');

INSERT INTO `t` VALUES ('8', '1', '11');

SQL 实现查询每个班级分数的前两名

-- 普通, 不能找出最大的人id, 也不能选择top N

select class, max(score) from t group by class order by score desc;

方法1

-- 分数大于等于自己的不能超过2人,那么剩下都是第一(大于自己的0人)和第二(大于自己的1人)

select t1.id,t1.class,t1.score

from t t1 left join t t2 on t1.class = t2.class and t1.score <= t2.score

group by t1.id, t1.class, t1.score

having count(t2.id) <= 2

order by t1.score desc;

-- 全展示出来便于理解

select t1.id,t1.class,t1.score,t2.id,t2.class,t2.score

from t t1 left join t t2 on t1.class = t2.class and t1.score <= t2.score

group by t1.id, t1.class, t1.score

having count(t2.id) <= 2

order by t1.score desc;

方法2

但是这是不对的, 不能用大于

-- 每个相同班级, 大于当前分数的同学 前两名 (至少大于一个人,如第二名大于第三名, 如果大于两人,第一名,如果大于0个人,第三名) ,

SELECT *

FROM t t1

WHERE (

SELECT COUNT(*)

FROM t t2

WHERE t2.class = t1.class

AND t2.score < t1.score

) >= 1

ORDER BY t1.score DESC

方法3

-- 每个相同班级, 小于当前分数的同学

-- 最多只能小于一个人,如第二名小于第一名, 如果小于超过1人,那么只能是第三名, 使用小于

SELECT *

FROM t t1

WHERE (

SELECT COUNT(*)

FROM t t2

WHERE t2.class = t1.class

AND t2.score > t1.score

) <= 1

ORDER BY t1.score DESC

查询结果