第四章 高级查询

4.1 简单子查询 究竟什么是子查询？子查询有什么用？带着这样的疑问，我们不妨先来解决本章第一个问题。 学员信息表（stuInfo）和学员成绩表（stuMarks）的数据。 stuNamestuNostuSexstuAgestuSeatstuAddress 张秋丽s25301男181北京海淀 李思文s25303女222河

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

　　　　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