mysql 中数据分组排序取前n条数据

mysql 中数据分组排序取前n条数据

1 sql语句

CREATE TABLE `stuscore`  (
  `userId` int(11) NOT NULL COMMENT '学生ID',
  `classId` int(11) NOT NULL COMMENT '班级',
  `score` int(255) NULL DEFAULT NULL COMMENT '得分',
  PRIMARY KEY (`userId`) USING BTREE
)  ;
 
-- ----------------------------
-- Records of stuscore
-- ----------------------------
INSERT INTO `stuscore` VALUES (101, 1, 100);
INSERT INTO `stuscore` VALUES (102, 2, 298);
INSERT INTO `stuscore` VALUES (103, 3, 105);
INSERT INTO `stuscore` VALUES (104, 1, 49);
INSERT INTO `stuscore` VALUES (105, 3, 200);
INSERT INTO `stuscore` VALUES (106, 2, 39);
INSERT INTO `stuscore` VALUES (107, 1, 300);
INSERT INTO `stuscore` VALUES (108, 2, 140);
INSERT INTO `stuscore` VALUES (109, 3, 168);
INSERT INTO `stuscore` VALUES (110, 3, 28);
INSERT INTO `stuscore` VALUES (111, 2, 234);
INSERT INTO `stuscore` VALUES (112, 1, 270);
INSERT INTO `stuscore` VALUES (113, 1, 345);
INSERT INTO `stuscore` VALUES (114, 2, 31);
INSERT INTO `stuscore` VALUES (115, 3, 242);
INSERT INTO `stuscore` VALUES (116, 3, 120);

2 按班级分组，取分数最高的n个人

– 需求：按班级分组，取分数最高的n个人
– ROW_NUMBER () over (partition by classId order by score desc)
– 按照班级分组，按照分数降序

2.1 按班级分组,排序

--step1 按班级分组,排序 
 select
      t.classId,
      t.userId,
      t.score,
      ROW_NUMBER () over (partition by classId order by score desc) rn
 from stuscore t

-- 查询结果
classId userId  score  rn
1		113		345		1
1		107		300		2
1		112		270		3
1		101		100		4
1		104		49		5
2		102		298		1
2		111		234		2
2		108		140		3
2		106		39		4
2		114		31		5
3		115		242		1
3		105		200		2
3		109		168		3
3		116		120		4
3		103		105		5
3		110		28		6

2.2 查询前3条

select 
  classId,
  userId,
  score
from (
 select
      t.classId,
      t.userId,
      t.score,
      ROW_NUMBER () over (partition by classId order by score desc) rn
 from stuscore t
 ) a 
where a.rn <= 3;-- 前三条 <= 3

-- 查询结果
classId	userId	score
1		113		345
1		107		300
1		112		270
2		102		298
2		111		234
2		108		140
3		115		242
3		105		200
3		109		168