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

发表于   |     |   阅读次数

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

查看SC表中的全部信息如下：

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

1
2
3
4
5
6
7
SELECT
  sc.SI 学号,
  COUNT(sc.CI) 选课门数
FROM
  sc,c
GROUP BY
  sc.SI

执行结果如下：
这里写图片描述

执行如下语句：

1
2
3
4
5
6
7
SELECT
  sc.SI 学号,
  COUNT(sc.CI) 选课门数
FROM
  sc
GROUP BY
  sc.SI

执行结果如下：
这里写图片描述

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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)
  )

解决方法二：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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查询的能力，但是要记得剔除重复数据