SQL求出分数都大于80的学生姓名及其总成绩

先看一下题目：

这是一道还算简单的题目，但是会困扰一些基础薄弱或刷题少的同学。

需要提前声明的是，这道题本身就存在问题，我理解的是这道题的数据库违反了数据库bcnf范式。也就是说对于成绩和学科这两个字段和学生信息放一起是有问题的，因为他们有依赖关系，简而言之就是，一门课程只能有一个分数。

那如果这道题就是考场上的题，我们就应该默认他是正确的来做。先看一下我准备的两张表。

数据库中的表

student表，记录学生姓名。

test表，记录学生科目及其成绩。

题目：

求出分数都大于80的学生姓名及其总成绩

难点是都大于80，而且是在这种设计有问题的表上查询（如果表设计的合理，这道题就是送分题）

看答案之前，大家不妨花时间做一下，其实很简单，如果你直接看答案不思考没什么意义！

解题思路：

首先要知道查的是什么，是学生姓名和总成绩（其实就是题目原话），但是多了个要求，所有成绩大于80

所以我们第一步要做的是过滤出所有大于80的学生信息。

select ……
from test t
where t.score > 80 
……

这步操作之后满足了这个必要条件，但是存在一个问题

如图，70分的记录会被过滤掉，这个学生的信息只会有数学这一课，但是我们要求输出的是所有科目都大于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 )

注意：这条sql的限制是，所有同学都参加了考试