SQL获取每个班级的前三名

最新推荐文章于 2024-07-06 00:33:08 发布

【星殒·化尘】

最新推荐文章于 2024-07-06 00:33:08 发布

阅读量1.9k

点赞数

文章标签： sql 数据库 java

本文链接：https://blog.csdn.net/qq_51413628/article/details/132438256

版权

查找每个班级分数最高的前三名
对于下表进行查询：
建表语句(建表语句来源于-无名小卒菜)：

create table TEST_SCORE
(
    CLASS VARCHAR(10),
    NAME  VARCHAR(10),
    SCORE INTEGER
);
 
INSERT INTO test_score ( class, name, score) VALUES ( '1班', '齐静春', 100);
INSERT INTO test_score ( class, name, score) VALUES ( '1班', '李二', 99);
INSERT INTO test_score ( class, name, score) VALUES ( '1班', '陈平安', 88);
INSERT INTO test_score ( class, name, score) VALUES ( '1班', '李柳', 98);
INSERT INTO test_score ( class, name, score) VALUES ( '1班', '赵耀', 79);
INSERT INTO test_score ( class, name, score) VALUES ( '2班', '岳东江', 81);
INSERT INTO test_score ( class, name, score) VALUES ( '2班', '苏杰', 69);
INSERT INTO test_score ( class, name, score) VALUES ( '2班', '吴杰', 87);
INSERT INTO test_score ( class, name, score) VALUES ( '2班', '王珂', 77);
INSERT INTO test_score ( class, name, score) VALUES ( '2班', '杜兰特', 99);
INSERT INTO test_score ( class, name, score) VALUES ( '2班', '罗斯', 90);
INSERT INTO test_score ( class, name, score) VALUES ( '3班', '马祖号', 69);
INSERT INTO test_score ( class, name, score) VALUES ( '3班', '王祖辉', 89);
INSERT INTO test_score ( class, name, score) VALUES ( '3班', '林冲', 91);
INSERT INTO test_score ( class, name, score) VALUES ( '3班', '赵卡', 80);
INSERT INTO test_score ( class, name, score) VALUES ( '3班', '罗帅', 87);
INSERT INTO test_score ( class, name, score) VALUES ( '4班', '陈旭', 77);
INSERT INTO test_score ( class, name, score) VALUES ( '4班', '王艳', 71);
INSERT INTO test_score ( class, name, score) VALUES ( '4班', '呼呼', 89);
INSERT INTO test_score ( class, name, score) VALUES ( '4班', '陈帅', 78);

在这里插入图片描述

答案：

select class, name,score
FROM test_score a
WHERE (
	SELECT COUNT(score) from test_score b WHERE b.class=a.class AND a.score<b.score
)<3
ORDER BY a.class,a.score DESC;

理解：进行内连接，对a表中的一行和内连接表的所有行相比较，若分数比该行它大的个数不超过三个（1个或者两个），则代表他是前三的，取出，按照班级和成绩逆序排序。

判断内连接后比它大的是否小于三个，若是，则where条件符合，取出该行。
(SELECT COUNT(score) from test_score b WHERE b.class=a.class AND a.score<b.score)<3
类似于两个for循环(伪代码)：
for(a){
	int cnt=0;
	for(b){
		if(a.score<b.score)cnt++;
	}
	if(cnt<3)cout<<a<<endl;(其实类似于加入到数组后再排序)
}