mysql分组排序

DROP TABLE IF EXISTS student_grade;
CREATE TABLE student_grade (
stuId int NULL DEFAULT NULL,
subId int NULL DEFAULT NULL,
grade int NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

---

-- Records of student_grade

---

INSERT INTO student_grade VALUES (1, 1, 91);
INSERT INTO student_grade VALUES (3, 1, 99);
INSERT INTO student_grade VALUES (2, 1, 92);
INSERT INTO student_grade VALUES (4, 1, 73);
INSERT INTO student_grade VALUES (3, 2, 95);
INSERT INTO student_grade VALUES (2, 2, 80);
INSERT INTO student_grade VALUES (4, 2, 78);
INSERT INTO student_grade VALUES (1, 2, 50);

SET FOREIGN_KEY_CHECKS = 1;

SQL写法:

SELECT
*
FROM
student_grade AS a
WHERE
( SELECT coun ( * ) FROM student_grade AS b WHERE b.subid = a.subid AND b.grade >= a.grade ) <= 2
ORDER BY
a.subid,
a.grade DESC
结果

理解：

核心思路：要算出某人成绩在第几名，可以转换成：算出他一共比多少人成绩高。比如，第一名的人，就没其它人成绩比他更好。第三名的人，就有两个人成绩比他好。

where语句可以理解为，把表中的每一行记录，都去与给定的where条件作对比，满足的再查出来。也就是有个遍历的过程。

模拟下SQL执行的过程就是，先取出外层a表的第一条记录

此外需要注意的是，该方法在有相同的多个数据时，会导致相同结果全部被选中，此时可以考虑采用另外的方法：

SELECT
s2.stuid,
s2.subid,
s2.grade
FROM
(
SELECT
IF
( s1.subid = @subid, @rank := @rank + 1, @rank := 1 ) AS rank,
@subid := subid AS tmp_subid,
s1.stuid,
s1.subid,
s1.grade
FROM
( SELECT stuid, subid, grade FROM student_grade ORDER BY subid, grade DESC ) s1,
( SELECT @subid := NULL, @rank := 1 ) tmp
) s2
WHERE
s2.rank <= 2
————————————————
版权声明：本文为CSDN博主「从未完美过」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_42056745/article/details/102860744

方法2

DROP TABLE IF EXISTS t_testscore;
CREATE TABLE t_testscore (
pk_id int NOT NULL,
c_name varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
c_score int NULL DEFAULT NULL,
c_class int NULL DEFAULT NULL,
PRIMARY KEY (pk_id) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

---

-- Records of t_testscore

---

INSERT INTO t_testscore VALUES (1, '张三6', 61, 1);
INSERT INTO t_testscore VALUES (2, '张三5', 65, 1);
INSERT INTO t_testscore VALUES (3, '张三4', 64, 1);
INSERT INTO t_testscore VALUES (4, '张三3', 63, 1);
INSERT INTO t_testscore VALUES (5, '张三2', 62, 1);
INSERT INTO t_testscore VALUES (11, '李四6', 76, 2);
INSERT INTO t_testscore VALUES (12, '李四5', 75, 2);
INSERT INTO t_testscore VALUES (13, '李四4', 74, 2);
INSERT INTO t_testscore VALUES (14, '李四3', 73, 2);
INSERT INTO t_testscore VALUES (15, '李四2', 72, 2);

select * from t_testscore a,(select SUBSTRING_INDEX(GROUP_CONCAT(a.pk_id order by c_score desc),',',3) pks from t_testscore a GROUP BY a.c_class) b where
FIND_IN_SET(pk_id,b.pks)