开发中经常遇到分组排序取前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);