MySql中group by分组排序取前n条数据

开发中经常遇到分组排序取前n条数据的需求，通常描述为：按某表格中某一个字段（或多个字段）分组，组内按某字段进行排序，并输出每组的前n条记录。针对这样的需求，在这里提供一种我认为最合理的方案。为了便于描述，假设我们针对下面这张表进行操作：

表名：StuScore，字段如下：

表中测试数据如下：

需求：按班级（classId）分组，按成绩（score）排序，取每个班前3名。

我们分两步完成：

1. 先分组排序，并给出分组排名newRank，SQL如下：

select -1 into @curClassId; 
select -1 into @rank;  
select *,		
   @rank := IF(@curClassId = classId, @rank + 1, 1) AS newRank,
	 @curClassId := classId 
from StuScore 
order by classId,score desc

结果如下：

这里增加了两个字段：newRank 和 @curClassId，其中我们感兴趣的只有newRank。根据这个字段，我们就能找到每组中排名前三的同学。

2.每组中取序号(newRank)小于等于3的数据。这个3也可以根据需求修改。

select -1 into @curClassId; 
select -1 into @rank; 
select classId,userId,score,newRank from
(select *,		
   @rank := IF(@curClassId = classId, @rank + 1, 1) AS newRank,
	 @curClassId := classId 
from StuScore 
order by classId,score desc ) aaa
where newRank <= 3
order by classId,newRank

结果如下：

需要说明的是：select -1 into @curClassId；目的是为@curClassId这个变量赋值。如果没有这条语句，在某些情况下，后面的SQL运行会出现排名全部为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
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- 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);