【数据库】sql错题详解

1. 执行子查询

SELECT 供应商号
FROM 订购单
WHERE 职工号 IN ('E1', 'E3')
GROUP BY 供应商号
HAVING COUNT(DISTINCT 职工号) = 2

• 筛选职工号为 E1 或 E3 的记录：
  依据 WHERE 职工号 IN ('E1', 'E3') 这个条件，从 订购单 表中把职工号为 E1 或者 E3 的记录筛选出来。得到的结果如下：
  | E3 | S7 | OR67 | 2002/06/23 |
  | E1 | S4 | OR73 | 2002/07/28 |
  | E3 | S4 | OR79 | 2002/06/13 |
  | E3 | S3 | OR91 | 2002/07/13 |

• 按 供应商号 分组：
  运用 GROUP BY 供应商号 对筛选出来的结果进行分组，分组情况如下：

  • 供应商号 S3：包含记录 (E3, S3, OR91, 2002/07/13)
  • 供应商号 S4：包含记录 (E1, S4, OR73, 2002/07/28) 和 (E3, S4, OR79, 2002/06/13)
  • 供应商号 S7：包含记录 (E3, S7, OR67, 2002/06/23)

• 使用 HAVING 子句筛选分组结果：
  HAVING COUNT(DISTINCT 职工号) = 2 的作用是只保留分组中不同职工号数量为 2 的分组。

  • 对于供应商号 S3，其分组里只有职工号 E3，不同职工号数量为 1，不满足条件。
  • 对于供应商号 S4，分组中有职工号 E1 和 E3，不同职工号数量为 2，符合条件。
  • 对于供应商号 S7，分组里只有职工号 E3，不同职工号数量为 1，不符合条件。
    所以，子查询最终返回的 供应商号 是 S4。

2. 执行主查询

SELECT *
FROM 供应商
WHERE 地址 = '北京'AND 供应商号 IN ('S4')

 

• 筛选地址为北京的供应商：
  根据 WHERE 地址 = '北京' 条件，从 供应商 表中筛选出地址为北京的供应商记录。结果如下：
  | 供应商号 | 供应商名 | 地址 |
  | S4 | 华通电子公司 | 北京 |
  | S7 | 爱华电子厂 | 北京 |

• 筛选 供应商号 在子查询结果中的记录：
  按照 AND 供应商号 IN ('S4') 条件，从上述筛选结果中进一步筛选出 供应商号 为 S4 的记录。最终结果如下：
  | 供应商号 | 供应商名 | 地址 |
  | S4 | 华通电子公司 | 北京 |

1. 子查询部分

SELECT 仓库号, AVG(工资) AS avg_salary
FROM 职工
GROUP BY 仓库号

• GROUP BY 仓库号：按照 仓库号 对 职工 表中的记录进行分组。也就是说，会把在同一个仓库工作的职工记录分为一组。
• SELECT 仓库号, AVG(工资) AS avg_salary：对于每一组，计算该组内职工工资的平均值，并将结果命名为 avg_salary，同时选择 仓库号 作为分组标识。例如，如果有两个仓库 WH1 和 WH2，那么会分别计算 WH1 仓库和 WH2 仓库中职工的平均工资。

 

这个子查询的结果是一个临时表，包含两列：仓库号 和该仓库对应的 avg_salary。

2. JOIN ON 部分

JOIN (-- 子查询SELECT 仓库号, AVG(工资) AS avg_salaryFROM 职工GROUP BY 仓库号
) s ON t.仓库号 = s.仓库号

 

• JOIN：JOIN 是用于将两个或多个表连接起来的操作符。在这里，将 职工 表（用别名 t 表示）和子查询结果（用别名 s 表示）进行连接。
• (...) s：子查询被当作一个临时表，使用别名 s 来引用它。
• ON t.仓库号 = s.仓库号：ON 关键字后面的条件指定了连接的规则。这里表示将 职工 表中的 仓库号 与子查询结果中的 仓库号 进行匹配，只有当两者相等时，对应的记录才会被连接在一起。例如，如果 职工 表中有一条记录的 仓库号 是 WH1，子查询结果中也有 WH1 对应的平均工资记录，那么这两条记录就会被连接起来。

3. 主查询和 WHERE 部分

SELECT t.*
FROM 职工 t
JOIN (SELECT 仓库号, AVG(工资) AS avg_salaryFROM 职工GROUP BY 仓库号
) s ON t.仓库号 = s.仓库号
WHERE t.工资 < s.avg_salary;

 

• SELECT t.*：选择 职工 表（别名 t）中的所有列。
• WHERE t.工资 < s.avg_salary：WHERE 子句用于筛选满足条件的记录。这里表示只选择 职工 表中工资小于其所在仓库平均工资的记录。

示例说明

假设 职工 表数据如下：

 

仓库号	职工号	工资
WH1	E1	2000
WH1	E2	3000
WH2	E3	4000
WH2	E4	5000

 

子查询计算出的平均工资结果如下：

 

仓库号	avg_salary
WH1	2500
WH2	4500

 

通过 JOIN ON 连接后，会将 职工 表和子查询结果进行匹配，得到：

 

仓库号	职工号	工资	avg_salary
WH1	E1	2000	2500
WH1	E2	3000	2500
WH2	E3	4000	4500
WH2	E4	5000	4500

 

最后，通过 WHERE 子句筛选出工资小于平均工资的记录，即：

 

仓库号	职工号	工资	avg_salary
WH1	E1	2000	2500
WH2	E3	4000	4500

 

这样就得到了工资低于所在仓库平均工资的职工信息。

FROM 职工：指定从  职工 表中获取数据。

 （7）检索学习全部课程的学生姓名

我们还是基于之前的示例数据来详细分析这个 SQL 语句每一步的执行结果。下面是涉及的三个表数据：

学生表 S

SNO	SNAME	AGE	SEX	SDEPT
S1	张三	20	男	计算机系
S2	李四	21	女	数学系
S3	王五	20	男	计算机系

课程表 C

CNO	CNAME	CDEPT	TNAME
C1	数据库原理	计算机系	赵老师
C2	高等数学	数学系	钱老师
C3	编程语言	计算机系	孙老师

学生选课表 SC

SNO	CNO	GRADE
S1	C1	85
S1	C3	90
S2	C2	78
S3	C1	88
S3	C2	92
S3	C3	80

1. 最内层子查询（针对每个学生和课程组合）

最内层子查询为 SELECT * FROM SC sc WHERE sc.SNO = s.SNO AND sc.CNO = c.CNO，它会针对每个学生和每门课程的组合进行查询，判断该学生是否选修了这门课程。

对于学生 S1

• 当课程为 C1 时，查询结果：
  | SNO | CNO | GRADE |
  | ---- | ---- | ---- |
  | S1 | C1 | 85 |
• 当课程为 C2 时，由于 S1 未选修 C2，查询结果为空表。
• 当课程为 C3 时，查询结果：
  | SNO | CNO | GRADE |
  | ---- | ---- | ---- |
  | S1 | C3 | 90 |

对于学生 S2

• 当课程为 C1 时，由于 S2 未选修 C1，查询结果为空表。
• 当课程为 C2 时，查询结果：
  | SNO | CNO | GRADE |
  | ---- | ---- | ---- |
  | S2 | C2 | 78 |
• 当课程为 C3 时，由于 S2 未选修 C3，查询结果为空表。

对于学生 S3

• 当课程为 C1 时，查询结果：
  | SNO | CNO | GRADE |
  | ---- | ---- | ---- |
  | S3 | C1 | 88 |
• 当课程为 C2 时，查询结果：
  | SNO | CNO | GRADE |
  | ---- | ---- | ---- |
  | S3 | C2 | 92 |
• 当课程为 C3 时，查询结果：
  | SNO | CNO | GRADE |
  | ---- | ---- | ---- |
  | S3 | C3 | 80 |

2. 中间层子查询（针对每个学生）

中间层子查询为 SELECT * FROM C c WHERE NOT EXISTS (SELECT * FROM SC sc WHERE sc.SNO = s.SNO AND sc.CNO = c.CNO)，它会针对每个学生，找出该学生未选修的课程。

对于学生 S1

因为 S1 未选修 C2，所以查询结果：

 

CNO	CNAME	CDEPT	TNAME
C2	高等数学	数学系	钱老师

对于学生 S2

因为 S2 未选修 C1 和 C3，所以查询结果：

 

CNO	CNAME	CDEPT	TNAME
C1	数据库原理	计算机系	赵老师
C3	编程语言	计算机系	孙老师

对于学生 S3

由于 S3 选修了所有课程，中间层子查询结果为空表。

3. 最外层查询

最外层查询为 SELECT SNAME FROM S s WHERE NOT EXISTS (SELECT * FROM C c WHERE NOT EXISTS (SELECT * FROM SC sc WHERE sc.SNO = s.SNO AND sc.CNO = c.CNO))，它会筛选出选修了所有课程的学生姓名。

 

• 对于学生 S1 和 S2，中间层子查询有结果返回，NOT EXISTS 为 FALSE，不会被选中。
• 对于学生 S3，中间层子查询结果为空表，NOT EXISTS 为 TRUE，会被选中。

 

最终的查询结果为：

 

SNAME
王五

（8）查询所学课程包含学生 S3 所学课程的学生学号

学生选课表 SC

SNO	CNO	GRADE
S1	C1	85
S1	C3	90
S2	C2	78
S3	C1	88
S3	C2	92
S3	C3	80

语句详解

1. 最外层查询：SELECT DISTINCT SNO FROM SC s1 WHERE... 从学生选课表 SC 中选择不同的学生学号 SNO，表 SC 使用别名 s1，并且后面跟着筛选条件。
2. 中间层子查询：SELECT 1 FROM SC s2 WHERE s2.SNO = 'S3' AND NOT EXISTS (...) 从学生选课表 SC（使用别名 s2）中筛选出学号为 S3 的记录，并且对于每一条这样的记录，再嵌套一个子查询进行进一步判断。
3. 最内层子查询：SELECT 1 FROM SC s3 WHERE s3.SNO = s1.SNO AND s3.CNO = s2.CNO 从学生选课表 SC（使用别名 s3）中查找是否存在满足学号等于外层 s1 的学号且课程号等于中间层 s2 的课程号的记录。如果存在，该子查询返回一行（值为 1），否则不返回行。

 

整体逻辑是：对于 SC 表中的每一个学生 s1，检查 S3 选修的每一门课程，看 s1 是否也选修了这门课程。如果 S3 选修的所有课程 s1 都选修了，那么 s1 就满足条件，会被选入最终结果。

每一步结果

1. 初始化外层循环，遍历 SC 表中的每一个学生（以 s1 表示）

   • 当 s1.SNO = S1 时：
     • 中间层子查询：SELECT 1 FROM SC s2 WHERE s2.SNO = 'S3'，得到 S3 选修课程的记录：
       | SNO | CNO | GRADE |
       | ---- | ---- | ---- |
       | S3 | C1 | 88 |
       | S3 | C2 | 92 |
       | S3 | C3 | 80 |
     • 对于上述每一条记录进行最内层子查询：
       • 当 s2.CNO = C1 时，SELECT 1 FROM SC s3 WHERE s3.SNO = 'S1' AND s3.CNO = 'C1'，有记录，返回一行（值为 1）。
       • 当 s2.CNO = C2 时，SELECT 1 FROM SC s3 WHERE s3.SNO = 'S1' AND s3.CNO = 'C2'，无记录，不返回行。
       • 当 s2.CNO = C3 时，SELECT 1 FROM SC s3 WHERE s3.SNO = 'S1' AND s3.CNO = 'C3'，有记录，返回一行（值为 1）。
     • 因为存在 S3 选修的课程（C2）而 S1 未选修，所以中间层子查询的 NOT EXISTS 部分对于 S1 为 FALSE，S1 不满足最外层查询条件。
   • 当 s1.SNO = S2 时：
     • 中间层子查询：SELECT 1 FROM SC s2 WHERE s2.SNO = 'S3'，得到 S3 选修课程的记录（同上面 S3 的选修课程记录）。
     • 对于上述每一条记录进行最内层子查询：
       • 当 s2.CNO = C1 时，SELECT 1 FROM SC s3 WHERE s3.SNO = 'S2' AND s3.CNO = 'C1'，无记录，不返回行。
       • 当 s2.CNO = C2 时，SELECT 1 FROM SC s3 WHERE s3.SNO = 'S2' AND s3.CNO = 'C2'，有记录，返回一行（值为 1）。
       • 当 s2.CNO = C3 时，SELECT 1 FROM SC s3 WHERE s3.SNO = 'S2' AND s3.CNO = 'C3'，无记录，不返回行。
     • 因为存在 S3 选修的课程（C1 和 C3）而 S2 未选修，所以中间层子查询的 NOT EXISTS 部分对于 S2 为 FALSE，S2 不满足最外层查询条件。
   • 当 s1.SNO = S3 时：
     • 中间层子查询：SELECT 1 FROM SC s2 WHERE s2.SNO = 'S3'，得到 S3 选修课程的记录（同上面 S3 的选修课程记录）。
     • 对于上述每一条记录进行最内层子查询：
       • 当 s2.CNO = C1 时，SELECT 1 FROM SC s3 WHERE s3.SNO = 'S3' AND s3.CNO = 'C1'，有记录，返回一行（值为 1）。
       • 当 s2.CNO = C2 时，SELECT 1 FROM SC s3 WHERE s3.SNO = 'S3' AND s3.CNO = 'C2'，有记录，返回一行（值为 1）。
       • 当 s2.CNO = C3 时，SELECT 1 FROM SC s3 WHERE s3.SNO = 'S3' AND s3.CNO = 'C3'，有记录，返回一行（值为 1）。
     • 因为 S3 选修的所有课程 S3 自己都选修了，所以中间层子查询的 NOT EXISTS 部分对于 S3 为 TRUE，S3 满足最外层查询条件。

2. 最终结果：
   经过最外层的 DISTINCT 处理后，得到满足条件的学生学号：
   | S3 |