2021-04-26

MySQL：分组求前几名的三种方式。

作为一名sqlman，经常遇见分组求前几名的需求，因此在这里整理三种常见的方式来处理这类问题。

一、数据准备

*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for studentgrade
-- ----------------------------
DROP TABLE IF EXISTS `studentgrade`;
CREATE TABLE `studentgrade`  (
  `stuId` char(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_german2_ci NOT NULL,
  `subId` int(255) NOT NULL,
  `grade` int(255) NULL DEFAULT NULL,
  PRIMARY KEY (`stuId`, `subId`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_german2_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of studentgrade
-- ----------------------------
INSERT INTO `studentgrade` VALUES ('001', 1, 97);
INSERT INTO `studentgrade` VALUES ('001', 2, 50);
INSERT INTO `studentgrade` VALUES ('001', 3, 70);
INSERT INTO `studentgrade` VALUES ('002', 1, 92);
INSERT INTO `studentgrade` VALUES ('002', 2, 80);
INSERT INTO `studentgrade` VALUES ('002', 3, 30);
INSERT INTO `studentgrade` VALUES ('003', 1, 93);
INSERT INTO `studentgrade` VALUES ('003', 2, 95);
INSERT INTO `studentgrade` VALUES ('003', 3, 85);
INSERT INTO `studentgrade` VALUES ('004', 1, 73);
INSERT INTO `studentgrade` VALUES ('004', 2, 78);
INSERT INTO `studentgrade` VALUES ('004', 3, 87);

SET FOREIGN_KEY_CHECKS = 1;

 二、三种方式分别为：利用子查询方法、自连接方法，自定义变量法。

一、子查询方法：该方法主要通过遍历索引的方式

SELECT 
    a.*
FROM studentgrade a
    where (SELECT count(1) FROM studentgrade b WHERE a.subid = b.subid and a.grade<b.grade) <= 2
ORDER BY a.subid,a.grade desc;
 

二、自连接方法

SELECT a.*
FROM studentgrade a left join studentgrade b
on a.subId = b.subId
and a.grade < b.grade
GROUP BY a.subId,a.grade
HAVING count(b.subid) <= 1
ORDER BY a.subId,a.grade desc;

第一种方式与第二种方式的思想相同，都是通过索引找到比当前索引的grade小的情况，通过施加约束来筛选top。

三、自定义变量

SELECT t.stuid,t.subid,t.grade,t.rank_no
FROM(
SELECT 
    a.*,
    if(@subid = a.subid,@rank := @rank+1,@rank:=1) rank_no,
    @subid := a.subid
FROM (SELECT * FROM studentgrade t ORDER BY t.subId,grade desc) a,(SELECT @subid := null,@rank := 1)b)t;

注意：需要先对原始表的数据进行排序：ORDER BY t.subId,grade desc