查询选修全部课程的学生姓名和所属单位

比眉伴天荒 2022-08-02 05:38 133阅读 0赞

##### 查询选修全部课程的学生姓名和所属单位 #####

在解答这个问题之前先举一个小例子，现有3张表  
S (SI,SN,SD,SA) SI,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄  
C (CI,CN ) CI,CN 分别代表课程编号、课程名称  
SC ( SI,CI,G ) SI,CI,G 分别代表学号、所选修的课程编号、学习成绩  
查看SC表中的全部信息如下：

![这里写图片描述][20150709225419125]  
要求返回每个SC表中每个学生选修的课数  
执行如下语句：

SELECT
        sc.SI 学号,
        COUNT(sc.CI) 选课门数
    FROM
        sc,c
    GROUP BY
        sc.SI

执行结果如下：  
![这里写图片描述][20150709230542010]

执行如下语句：

SELECT
        sc.SI 学号,
        COUNT(sc.CI) 选课门数
    FROM
        sc
    GROUP BY
        sc.SI

执行结果如下：  
![这里写图片描述][20150709230604740]

相信看到这里应该知道我要说的是什么问题了，可以看到**两次查询语句的唯一差别就是 from sc和from sc,c**，然而结果却相差很多，这个就是由于关联查询造成的，从sc、c两张表中选择时结果会存在很多重复的行，所以需要剔除重复的行，因此在查询时如果涉及到多张表，使用group by分组获取数据时一定要记得剔除重复数据。  
**下面解决第一个问题**  
使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位  
解决方法1：

先在sc表中按学号分组找出选修所有课程的学生的学号，然后在S中通过SI去取学员学号和所属单位

SELECT
        s.SN 姓名,
        s.SD 单位
    FROM
        s
    WHERE
        s.SI IN (
            SELECT
                sc.SI
            FROM
                sc,
                c
            GROUP BY
                sc.SI
            HAVING
                COUNT(DISTINCT sc.CI) = (SELECT COUNT(*) FROM c)
        )

解决方法二：

SELECT
        s.SI,
        s.SD
    FROM
        s
    WHERE
        NOT EXISTS (
            SELECT
                *
            FROM
                c
            WHERE
                NOT EXISTS (
                    SELECT
                        *
                    FROM
                        sc
                    WHERE
                        CI = s.SI
                    AND CI = c.CI
                )
        )

**两种方法相比，第一种更加灵活易懂，更加考验了使用SQL查询的能力，但是要记得剔除重复数据**

[20150709225419125]: /images/20220731/cd32153df073403584154e2742c26c4c.png
[20150709230542010]: /images/20220731/a72f09ee979541fcbe7d1d766469e405.png
[20150709230604740]: /images/20220731/cd959bacf0494d1cbc045acb67dc5379.png