第四章 高级查询
4.1 简单子查询
究竟什么是子查询？子查询有什么用？带着这样的疑问，我们不妨先来解决本章第一个问题。
学员信息表（stuInfo）和学员成绩表（stuMarks）的数据。
| stuName | stuNo | stuSex | stuAge | stuSeat | stuAddress |
| 张秋丽 | s25301 | 男 | 18 | 1 | 北京海淀 |
| 李思文 | s25303 | 女 | 22 | 2 | 河南洛阳 |
| 李文才 | s25302 | 男 | 85 | 3 | 地址不详 |
| 欧阳俊雄 | s25304 | 男 | 28 | 4 | 新疆 |
| 梅超风 | s25318 | 女 | 23 | 5 | 地址不详 |
| ExamNo | stuNo | writtenExam | labExam |
| s271811 | s25303 | 90 | 58 |
| s271813 | s25302 | 60 | 90 |
| s271816 | s25301 | 87 | 82 |
问题：查看年龄比“李思文”大的学员，要求显示这些学员的信息。
实现方法一：采用T-SQL变量实现，SQL语句如示例1所示。
示例1
Declare @age int ---定义变量，用于存放“李思文”的年龄
Select @age = stuAge From stuInfo where stuName = '李思文' ---求出“李思文”的年龄
Select * from stuInfo where stuAge > @age ---筛选比“李思文”年龄大的学员
GO
实现方法二：采用子查询实现，SQL语句如示例2所示。
我们可以合并上述两步。
将语句 Select * from stuInfo where stuAge > @age
替换为如示例2所示的语句
示例2
Select * from stuInfo Where stuAge > ( select stuAge from stuInfo where stuName > '李思文')
Go
你一定明白了，上述查询中的“( select stuAge from stuInfo where stuName > '李思文')”部分，就是子查询，因为它嵌入到查询中作为Where条件的一部分。
所以它在Where语句中的一般用法如下。
Select ··· from 表1 Where 字段1 > (子查询)
除了“>” 号外，还可以使用其他运算符号，习惯上，外面的查询称为父查询，括号中嵌入的查询称为子查询。SQL Server 执行时，先执行子查询部分，求出子查询部分的值，然后再执行整个父查询。它的执行效率比采用SQL变量实现的方案要高，所以推荐采用子查询。因为子查询作为Where条件的一部分，所以还可以和Update、Insert、Delete一起使用，语法类似于Select语句。
注： 将子查询和比较运算符联合使用，必须保证子查询返回的值不能多于一个。
上述子查询将多个结果集合并在一起，除此之外，还可以将多表间的数据组合在一起，从而替换连接（Join）查询。
问题：查询笔试刚好通过60分的学员名单。
实现方法一： 采用表连接。
示例3
Select stuName from stuInfo inner join stuMarks
On stuInfo.stuNo = stuMarks.stuNo where writtenExam = 60
GO
除了采用表连接以外，还可以采用子查询替换上述连接。
实现方法二：采用子查询
示例4
Select stuName From stuInfo where stuNo = (select stuNo from stuMarks where writtenExam = 60) GO
注：一般来说，表连接都可以用子查询替换，但反过来说却不一定，有的子查询不能用表连接来替换。子查询比较灵活、方便，形式多样，适合于作为查询的筛选条件。而表连接更适合于查看多表的数据。
4.2 IN 和 Not In 子查询
使用“=”、“>” 等比较运算符时，要求子查询只能返回一条或空的记录。在SQL Server中，当子查询跟随在 = 、!= 、<、<=、>、>=之后，不允许子查询返回多条记录。例如上例查询笔试刚好及格的学员信息，成绩表中刚好只有一条记录满足条件：李文才（s25302）的笔试成绩刚好60分。如果有多条记录满足条件，既有多个学员的笔试成绩为60分，采用上述子查询将出现编译错误。
示例5
/*-- 采用in子查询查询参加考试的学员名单 --*/
Select stuName from stuInfo where stuNo in (select stuNo from stuMarks) GO
同理，如果希望查询未参加考试的学员名单呢？您一定想到了，加上否定的Not 即可。
/*-- 采用Not in 子查询，查看未参加考试的学员名单 --*/
Select stuName from stuInfo where stuNo Not in (select stuNo from stuMarks) GO
4.3 Exists 和 Not Exists 子查询
Exists 语句我们并不陌生，在学习创建库和创建表的语句时曾提前用过，它是一个存在检测的子查询语句。例如，如果存在数据库stuDB，则先删除它，然后重新创建。
IF Exists (select * from sysDatabases where name = 'stuDB')
Drop DataBase stuDB
Create DataBase stuDB
··· 创建的库代码略
从理论上讲，Exists 也可以作为Where语句的子查询，但一般用于IF语句的存在检测。其基本语法如下：
IF Exists(子查询)
语句
如果子查询的结果非空，则Exists(子查询)将返回真(true)，否则返回假(false)。
问题：检查本次考试，本班如果有人笔试成绩达到80分以上，则每人提2分，否则，每人允许提5分。
示例8
/*-- 采用Exists子查询，进行酌情加分 --*/
IF Exists (select * from stuMarks where writtenExam > 80)
Begin
print '本班有人笔试成绩高于80分，每人只加2分，加分后的成绩为：'
Update stuMarks set writtenExam = writtenExam + 2
Select * from stuMarks
End
Else
Begin
print '本班无人笔试成绩高于80分，每人可以加5分，加分后的成绩为：'
Update stuMarks set writtenExam = writtenExam + 5
Select * from stuMarks
End
Go
Exists 和 in 一样，同样允许添加Not取反，表示不存在。
问题，检查本次考试，本班如果没有一人通过考试（笔试和机试成绩都>60分），则试题偏难，每人加3分，否则，每人只加1分。
示例9
/*--- 采用Not Exists子查询，根据试题难度加分 ---*/
If Not Exists ( select * from stuMarks where writtenExam > 60 and LabExam > 60)
Begin
Print '本班无人通过考试，试题骗难，每人加3分，加分后成绩为：'
Update stuMarks set writtenExam = writtenExam + 3 , labExam = labExam + 3
Select * from stuMarks
End
Else
Begin
Print '本班考试成绩一般，每人只加1分，加分后的成绩为：'
Update stuMarks set writtenExam = writtenExam + 1, labExam = labExam + 1
Select * from stuMarks
End
Go
4.4 T-SQL 语句的综合应用
假定目前本次考试学员信息表(stuInfo)和学员成绩表(stuMarks)的原始数据如下：
| stuName | stuNo | stuSex | stuAge | stuSeat | stuAddress |
| 张秋丽 | s25301 | 男 | 18 | 1 | 北京海淀 |
| 李思文 | s25303 | 女 | 22 | 2 | 河南洛阳 |
| 李文才 | s25302 | 男 | 85 | 3 | 地址不详 |
| 欧阳俊雄 | s25304 | 男 | 28 | 4 | 新疆 |
| 梅超风 | s25318 | 女 | 23 | 5 | 地址不详 |
| ExamNo | stuNo | writtenExam | LabExam |
| s271811 | s25303 | 93 | 59 |
| s271813 | s25302 | 63 | 91 |
| s271816 | s25301 | 90 | 83 |
| s271817 | s25318 | 63 | 53 |
问题：
（1）统计本次考试的缺考情况，结果如图第一个记录集所示。
| 应到人数 | 实到人数 | 缺考人数 |
| 5 | 4 | 1 |
| 姓名 | 学号 | 笔试成绩 | 机试成绩 | 是否通过 |
| 张秋丽 | s25301 | 90 | 89 | 是 |
| 李思文 | s25303 | 93 | 65 | 是 |
| 李文才 | s25302 | 63 | 97 | 是 |
| 欧阳俊雄 | s25304 | 缺考 | 缺考 | 否 |
| 梅超风 | s25318 | 63 | 59 | 否 |
| 总人数 | 通过人数 | 通过率 |
| 5 | 3 | 60% |
（2）提取学员的成绩信息并保存结果，包括学员姓名、学号、笔试成绩、机试成绩、是否通过。
（3）比较笔试平均分和机试平均分，较低者进行循环提分，但提分后最高分不能超过97分。
（4）提分后，统计学员的成绩和通过情况，如上图第二个记录集所示。
（5）提分后统计学员的通过率情况，如上图第三个记录集所示。
示例10
/*--- 本次考试的原始数据 ---*/
select * from stuInfo
select * from stuMarks
/*--- 统计考试缺考情况 ---*/
select 应到人数=(select Count(*) from stuInfo),
实到人数=(select count(*) from stuMarks),
缺考人数=((select count(*) from stuInfo) - (select count(*) from stuMarks))
/*--- 统计考试通过情况，并将统计结果存放在新表newTable中 ---*/
IF Exists (select * from sysobjects where name = 'newTable')
Drop table newTable
Select stuName, stuInfo.stuNo, writtenExam, labExam,
isPass = Case
When writtenExam >= 60 and labExam >=60 Then 1
Else 0
End
Info newTable from stuInfo Left Join stuMarks
On stuInfo.stuNo = stuMarks.stuNo
/*--- 酌情加分：比较笔试和机试平均分，哪科偏低，就给哪科提分 ---*/
Declare @avgWritten numeric (4,1), @avgLab numeric(4,1) ---定义变量存放笔试和机试平均分
Select @avgWritten = Avg(writtenExam) from newTable where writtenExam IS Not null
Select @avgLab = Avg(labExam) from newTable where labExam IS Not null
IF @avgWritten<@avgLab ---比较笔试和机试平均分，看哪科偏低
While (1=1) ---循环给笔试加分，最高分不能超过97分
Begin
Update newTable set writtenExam = writtenExam + 1
If(select Max(writtenExam) From newTable ) >= 97
Break
End
Else
While(1=1) ---循环给机试加分，最高分不能超过97分
Begin
Update newTable set labExam = labExam + 1
If(select Max(labExam) from newTable) >= 97
Break
End
--- 因为提分，所以需要更新isPass（是否通过）列的数据
Update newTable
Set isPass = Case
When writtenExam >= 60 and labExam >= 60 Then 1
Else 0
End
/*--- 显示考试最终通过情况 ---*/
Select 姓名=stuName, 学号=stuNo
笔试成绩=Case
when writtenExam IS null then '缺考'
Else convert(varchar(5), writtenExam)
End
机试成绩=Case
When labExam IS null then '缺考'
Else convert(varchar(5),labExam)
End
是否通过=Case
When isPass = 1 then '是'
Else '否'
End
From newTable
/*--- 显示通过率及通过人数 ---*/
Select 总人数 = Count(*) , 通过人数 = Sum(isPass),
通过率 = (convert (varchar(5), avg(isPass*100)) + '%' ) From newTable
Go
