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

浅浅的花香味﹌ 2021-11-23 09:48 338阅读 0赞

### 文章目录 ###

*   *  数据库中的表
     *  题目：
     *  解题思路：
     *  答案：

先看一下题目：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5MzUwMQ_size_16_color_FFFFFF_t_70]  
这是一道还算简单的题目，但是会困扰一些基础薄弱或刷题少的同学。

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

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

## 数据库中的表 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5MzUwMQ_size_16_color_FFFFFF_t_70 1]  
student表，记录学生姓名。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5MzUwMQ_size_16_color_FFFFFF_t_70 2]  
test表，记录学生科目及其成绩。

## 题目： ##

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

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

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

## 解题思路： ##

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

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

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

这步操作之后满足了这个必要条件，但是存在一个问题  
![在这里插入图片描述][20190726123110895.png]  
如图，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 )

![在这里插入图片描述][20190726123941361.png]  
注意：这条sql的限制是，所有同学都参加了考试

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5MzUwMQ_size_16_color_FFFFFF_t_70]: /images/20211122/3120102137f3426db3a93536ba520291.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5MzUwMQ_size_16_color_FFFFFF_t_70 1]: /images/20211122/d6ddfd5e93f94c33806968360c8d8f07.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5MzUwMQ_size_16_color_FFFFFF_t_70 2]: /images/20211122/22d9e053ecc540d1a37140796360ddda.png
[20190726123110895.png]: /images/20211122/7ce4f0376f8242f699ba8bedbaf24223.png
[20190726123941361.png]: /images/20211122/64c3fb06063b4ff29fbe219174983d4f.png