Mysql group by 后取 top N 问题

为什么80%的码农都做不了架构师？>>>   

测试数据

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

查询结果

转载于:https://my.oschina.net/ktlb/blog/1588653