MySQL中查询获取每个班级成绩前三名的学生信息
CREATE TABLE t_testscore(
pk_id INT PRIMARY KEY,
c_name VARCHAR(50) ,
c_score INT,
c_class INT) DEFAULT CHARSET=utf8;
INSERT INTO t_testscore VALUES (1, '张三6', 66, 1),(2, '张三5', 65, 1),(3, '张三4', 64, 1), (4, '张三3', 63, 1),(5, '张三2', 62, 1);
INSERT INTO t_testscore VALUES (11, '李四6', 76, 2),(12, '李四5', 75, 2),(13, '李四4', 74, 2), (14, '李四3', 73, 2),(15, '李四2', 72, 2);
SELECT * FROM t_testscore t
WHERE EXISTS(
SELECT COUNT(*) FROM t_testscore ts
WHERE ts.c_score>=t.c_score
GROUP BY ts.c_class
HAVING COUNT(*)<=3
)
ORDER BY c_class,c_score DESC;
group by 与 having count 学习:https://blog.csdn.net/qq_39443687/article/details/81025924
where exists 学习:https://www.cnblogs.com/beijingstruggle/p/5885137.html
如果 where exsits,having 不好理解的话,也可以下面这样写:
SELECT *
FROM `t_testscore` s1
WHERE (
SELECT COUNT( 1 )
FROM `t_testscore` s2
WHERE s1.c_class = s2.c_class
AND s2.c_score >= s1.c_score
) <=3
ORDER BY s1.`c_class` , s1.`c_score` DESC
LIMIT 0 , 30
第一个where 条件 后面的内容可以这样理解
s1: s2:
班级 分数 count(1) : s2分数 >= s1的分数的人数
1 66 1
1 65 2
1 64 3
1 63 4
... ... ....
2 76 1
2 75 2
... ... ...
再看上面sql 的括号里<=3 取的就是count(1) <= 3 的记录了 。