SQL语句中的嵌套子查询

         一开始在学习的SQL语句的时候，没有感受到嵌套子查询的厉害，尤其是相关子查询。现在发现它的厉害之处，写下来记录！

相关子查询

         先抛出一个问题来引出这个话题。查找每个学生超过他自己选修课程平均成绩的课程号。看到这个问题，首先有两点我们是不知道的。第一：每一个学生的到底选了什么课程。（有人可能会说i=，选课表SC不就告诉你了吗？的确，选修表SC是告诉我们了，但是我们也得去查哈。SC又不是已经把每一个学生的选课都变成了一张表，你直接select *就完了。所以，这里我们认为每一个学生到底选了什么课程，还是未知的）。第二：我们不知道学生的选课的平均成绩。

         那么，问题出来了。思路也就出来了。我们首先求得每一个学生的选课记录，然后取其平均值。然后要每个学生的每一门选课都和自己的平均成绩去比较，如果高出平均成绩就放入结果集。现在，给出SQL语句：

select Sno,Cno
from tb_SC x
where Grade >(
select AVG(Grade)
from tb_SC y
where x.Sno=y.sno
)

这个是tb_SC表的部分数据

它的执行流程我觉得是这样的：
首先，从x(tb_SC)表中拿出一条记录，例如第一条数据。然后用这条数据和内层查询的y(tb_SC)表中的每一条数据做比较，如果满足x.Sno=y.Sno，就抽出来到tmp表中去（这个tmp表是我自己想出来的，并于理解）。直到把y表的数据比配完后，tmp表中的就是所有20173824001的学生的选课记录了。然后使用内置函数avg得到平均分。返回给上层循环。然后去判断第一条记录的Grade是否大于平均分。之后的每条记录也可使用类似的方法分析。

         其实每一个相关子查询就是一个二重for循环。上面的例子使用c语言来描述的话：

static i=0;
for(;i<x.length;i++)
{	
	for(int j=0;j<y.length;j++)
	{
		int index=0;
		if(x.Sno==y.Sno)
		{
			tmp[index]=y[j].Grade;
			index++;
		}
	}
	//这里的return avg(tmp)按在c语言中可能有点歧义，大家能理解就好
	return avg(tmp);
}

         写一个我当时觉得正确的SQL语句，也是针对这题的：

select Sno,Cno
from tb_SC
where Grade >(
select AVG(y.Grade)
from tb_SC x,tb_SC y
where x.Sno=y.sno
)

我当时就觉得，为什么一定要使用相关子查询呢？不使用相关子查询也没有问题啊。但是事实告诉我是有问题的。上面的SQL语句计显示出来的结果并不是真正的结果。所以，我就发现了一个规律：什么时候使用相关子查询： 如果你想要使用一个表中的数据逐个和另一个表中的数据比较，这个时候可以使用相关子查询。就相当于二重for循环。

         那再来一个高级一点的例子，难度大一点的。求：选修了所有课程的学生的学号和姓名。这里我们再来分析一哈未知数。第一：有多少门选修课程我们不知道（可以使用Course表得到）。第二：学生选了哪几门课我们不知道（可以通过SC表得到）。因为SQL中是没有全称量词的（这里就是“所有”），所有我们只能通过存在量词等价转化为全称量词。那么这里就是：“没有一门课是他不选修的！”代表的就是这个学生选修了所有的课程。给出SQL语句：

select Sno,Sname
from tb_Student
where not exists
(	
	select *
	from tb_Course
	where not exists
	(
	select *
	from tb_SC
	where Sno=tb_Student.Sno
	and Cno=tb_Course.Cno
	)
)

         这里的意思就是说：

1. 从tb_Student中拿出一条数据
   1.1 然后从tb_Course表中拿出一条数据
2. 然后用这两条数据去tb_SC表中看有没有有这样的记录存在。即Sno=tb_Student.sno的同时，Cno=tb_Course.Cno
3. 如果没有这样的数据，说明这个学生没有选修这门课，所有最内存循环为false。导致最内层的not exists返回ture.这样子，最外层的not exists返回false。那么，这条记录就不能放到最终结果集中。
4. 如果有这样的一条记录，证明这个学生选过这门课，那么返回到第1.1步，然后取出tb_Course中的第二条数据。

我这里其实是有一个疑问的： 在步骤3中，如果这个学生没有选修这门课，那么这个最佳情况应该直接跳到第1步，然后取出二条tb_Student的数据。但是DBMS内部是不是这样做的，这个我就不知道了。我觉得应该不是这样做的吧。也希望大佬们在下面留言，说说自己的看法。

然后这里给出一种使用除法的思想的SQL语句：

select Sno
from tb_SC as SC_1
where not exists(
select Cno
from tb_Course
except
select Cno
from tb_SC as SC_2
where SC_1.Cno=SC_2.Cno)

自身连接

         最后再来说一哈关于自连接的小问题。这个就是为了之后复习的时候，不要再犯这么低级的错误。题目问的是：既选修了0002也选修了0004号课程的学生。我一开始写的SQL是这样的：

select  Sno
from tb_SC
where Cno='0002' and Cno='0004';

但是这个明显就有一个问题，怎么可能会有一个Cno在等于0002的同时，也等于0004。所以这样的SQL语句的出来的结果必然是空集。正确的结果是这样的：

select  x.Sno
from tb_SC x,tb_SC y
where x.Sno=y.Sno and x.Cno='0002' and y.Cno='0004';

就是自连接的表格可能我一开始没有想像到。例如：

就是这样的，当然我也没有全部弄出来。大概的意思应该可以看懂。这个的缺点就是有一些没有用处的的组合也出来了，当然这个也是无法避免的。

         还有一个要注意的问题就是：这里自身连接的条件是x.Sno=y.Sno；不是x.Cno=y.Cno；是因为你是要同一个人既选修0002，也选修0004。只有x.Sno=y.Sno的时候，一条元组才会代表一个人同时选修的课程，如果是x.Cno=y.Cno，代表的是这一门课同时被几个人选修！