题1：查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数_#01、查询“01”课程比“02”课程成绩高的学生的信息及课程分数-CSDN博客

本文链接：https://blog.csdn.net/q370835062/article/details/90167560

这篇博客详细介绍了如何使用SQL查询成绩表，找出'01'课程成绩高于'02'课程的学生信息。讨论了四种不同情况的查询方法，包括同时存在两门课程、只存在'01'课程、只存在'02'课程的情况，重点讲解了SELECT、WHERE和JOIN的用法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

题1

查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数
查询同时存在" 01 “课程和” 02 "课程的情况
查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )
查询不存在" 01 “课程但存在” 02 "课程的情况

解释

题1.1：查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数

首先明确我们需要查询的表是成绩表（SC）的信息。

最简单的查询是课程01和课程02的信息有哪些，例如查询课程01

mysql> SELECT * FROM  SC WHERE SC.CId="01";
+------+------+-------+
| SId  | CId  | score |
+------+------+-------+
| 01   | 01   |  80.0 |
| 02   | 01   |  70.0 |
| 03   | 01   |  80.0 |
| 04   | 01   |  50.0 |
| 05   | 01   |  76.0 |
| 06   | 01   |  31.0 |
+------+------+-------+
6 rows in set (0.01 sec)

其实就是通过SC表得到2张子表，笛卡儿积的方法合并两张表，然后通过WHERE条件进行筛选

mysql> SELECT *  FROM (SELECT * FROM  SC WHERE SC.CId="01") AS biao01,(SELECT * FROM  SC WHERE SC.CId="02") AS biao02  WHERE biao01.score > biao02.score AND biao01.SId = biao02.SId;
+------+------+-------+------+------+-------+
| SId  | CId  | score | SId  | CId  | score |
+------+------+-------+------+------+-------+
| 02   | 01   |  70.0 | 02   | 02   |  60.0 |
| 04   | 01   |  50.0 | 04   | 02   |  30.0 |
+------+------+-------+------+------+-------+
2 rows in set (0.00 sec)

题1.2：查询同时存在" 01 “课程和” 02 "课程的情况

这个跟题目1的非常类似

首先也是要明确查询的表是成绩表（SC）的信息。

我们先通过两张子表得到课程01和课程02的成绩信息，然后笛卡儿积合并两张表，最后WHERE金星筛选

mysql> SELECT * FROM (SELECT * FROM  SC WHERE SC.CId="01") AS biao01,(SELECT * FROM  SC WHERE SC.CId="02") AS biao02 WHERE biao01.SId = biao02.SId;
+------+------+-------+------+------+-------+
| SId  | CId  | score | SId  | CId  | score |
+------+------+-------+------+------+-------+
| 01   | 01   |  80.0 | 01   | 02   |  90.0 |
| 02   | 01   |  70.0 | 02   | 02   |  60.0 |
| 03   | 01   |  80.0 | 03   | 02   |  80.0 |
| 04   | 01   |  50.0 | 04   | 02   |  30.0 |
| 05   | 01   |  76.0 | 05   | 02   |  87.0 |
+------+------+-------+------+------+-------+
5 rows in set (0.00 sec)

题1.3：查询存在" 01 “课程但可能不存在” 02 "课程的情况（不存在时显示为 null ）

首先也是要明确查询的表是成绩表（SC）的信息。
这里的主要难点是：不存在显示为NULL，详单于做这件事：

mysql> SELECT *  FROM (SELECT * FROM  SC WHERE SC.CId="01") AS biao01 LEFT JOIN (SELECT * FROM  SC WHERE SC.CId="02") AS biao02 ON biao01.SId = biao02.SId; 
+------+------+-------+------+------+-------+
| SId  | CId  | score | SId  | CId  | score |
+------+------+-------+------+------+-------+
| 01   | 01   |  80.0 | 01   | 02   |  90.0 |
| 02   | 01   |  70.0 | 02   | 02   |  60.0 |
| 03   | 01   |  80.0 | 03   | 02   |  80.0 |
| 04   | 01   |  50.0 | 04   | 02   |  30.0 |
| 05   | 01   |  76.0 | 05   | 02   |  87.0 |
| 06   | 01   |  31.0 | NULL | NULL |  NULL |
+------+------+-------+------+------+-------+
6 rows in set (0.00 sec)

题1.4：查询不存在" 01 “课程但存在” 02 "课程的情况

首先也是要明确查询的表是成绩表（SC）的信息。

方法1

查询出01课程的学生SId信息

mysql> SELECT SId FROM  SC WHERE SC.CId="01";
+------+
| SId  |
+------+
| 01   |
| 02   |
| 03   |
| 04   |
| 05   |
| 06   |
+------+
6 rows in set (0.00 sec)

然后通过WHERE，NOT IN进行筛选，由于只需要课程02，所以加多一层条件

mysql> SELECT * FROM SC WHERE SC.SId NOT IN (SELECT SId FROM  SC WHERE SC.CId="01") AND SC.CId="02";
+------+------+-------+
| SId  | CId  | score |
+------+------+-------+
| 07   | 02   |  89.0 |
+------+------+-------+
1 row in set (0.00 sec)

方法2

类似题3，我们使用左链接的方法，然后排除掉为NULL的情况不就可以了？

mysql> SELECT *  FROM (SELECT * FROM  SC WHERE SC.CId="02") AS biao01 LEFT JOIN (SELECT * FROM  SC WHERE SC.CId="01") AS biao02 ON biao01.SId = biao02.SId;
+------+------+-------+------+------+-------+
| SId  | CId  | score | SId  | CId  | score |
+------+------+-------+------+------+-------+
| 01   | 02   |  90.0 | 01   | 01   |  80.0 |
| 02   | 02   |  60.0 | 02   | 01   |  70.0 |
| 03   | 02   |  80.0 | 03   | 01   |  80.0 |
| 04   | 02   |  30.0 | 04   | 01   |  50.0 |
| 05   | 02   |  87.0 | 05   | 01   |  76.0 |
| 07   | 02   |  89.0 | NULL | NULL |  NULL |
+------+------+-------+------+------+-------+
6 rows in set (0.00 sec)

mysql> SELECT *  FROM (SELECT * FROM  SC WHERE SC.CId="02") AS biao01 LEFT JOIN (SELECT * FROM  SC WHERE SC.CId="01") AS biao02 ON biao01.SId = biao02.SId WHERE biao02.SId IS NULL; 
+------+------+-------+------+------+-------+
| SId  | CId  | score | SId  | CId  | score |
+------+------+-------+------+------+-------+
| 07   | 02   |  89.0 | NULL | NULL |  NULL |
+------+------+-------+------+------+-------+
1 row in set (0.00 sec)