| SQL求出分数都大于80的学生姓名及其总成绩 | 您所在的位置：网站首页 › 高级筛选成绩大于80 › SQL求出分数都大于80的学生姓名及其总成绩 |
SQL求出分数都大于80的学生姓名及其总成绩
|
文章目录
数据库中的表题目：解题思路：答案：
先看一下题目： 需要提前声明的是，这道题本身就存在问题，我理解的是这道题的数据库违反了数据库bcnf范式。也就是说对于成绩和学科这两个字段和学生信息放一起是有问题的，因为他们有依赖关系，简而言之就是，一门课程只能有一个分数。 那如果这道题就是考场上的题，我们就应该默认他是正确的来做。先看一下我准备的两张表。 数据库中的表
求出分数都大于80的学生姓名及其总成绩 难点是都大于80，而且是在这种设计有问题的表上查询（如果表设计的合理，这道题就是送分题） 看答案之前，大家不妨花时间做一下，其实很简单，如果你直接看答案不思考没什么意义！ 解题思路：首先要知道查的是什么，是学生姓名和总成绩（其实就是题目原话），但是多了个要求，所有成绩大于80 所以我们第一步要做的是过滤出所有大于80的学生信息。 select …… from test t where t.score > 80 ……这步操作之后满足了这个必要条件，但是存在一个问题 所以，下一步要做的是，过滤掉第一次过滤后，对每个学生分组，过滤掉科目数量不满足条件的信息 select …… from test t where t.score > 80 GROUP BY t.sid HAVING COUNT( 1 ) = 2 ……那下一个问题，假如我们不知道科目只有2科怎么办，这时候就体现出这个表设计的问题，如果是分离的表可以直接统计，但是现在要做一个子查询，具体内容为，我们默认所有同学都参加了考试，所以，每个学生的考试科目数是相同的，可以对每个学生分组，对科目做count（），也就是查看每个学生考了几个科目。 由于这么查询以后，每个学生都对应一条记录，即 小明-2科，小红-2科，小王-2科（其实不会显示姓名，这里是便于理解），所以我们要做的是去重，或者只取第一条记录。 select …… from test t where t.score > 80 GROUP BY t.sid HAVING COUNT( 1 ) = ( SELECT DISTINCT ( COUNT( 1 ) ) FROM test GROUP BY sid ) ……这样子，基本sql写完，下一步是表关联查询，非常简单，不多说了。 答案： SELECT s.name,SUM( t.score ) FROM test t,student s WHERE t.sid = s.id AND t.score > 80 GROUP BY t.sid HAVING COUNT( 1 ) = ( SELECT DISTINCT ( COUNT( 1 ) ) FROM test GROUP BY sid )
|
这是一道还算简单的题目，但是会困扰一些基础薄弱或刷题少的同学。
student表，记录学生姓名。
test表，记录学生科目及其成绩。
如图，70分的记录会被过滤掉，这个学生的信息只会有数学这一课，但是我们要求输出的是所有科目都大于80的信息。
注意：这条sql的限制是，所有同学都参加了考试【本文地址】