4.1 简单子查询 究竟什么是子查询？子查询有什么用？带着这样的疑问，我们不妨先来解决本章第一个问题。 学员信息表（stuInfo）和学员成绩表（stuMarks）的数据。 stuNamestuNostuSexstuAgestuSeatstuAddress 张秋丽s25301男181北京海淀 李思文s25303女222河
4.1 简单子查询
究竟什么是子查询？子查询有什么用？带着这样的疑问，我们不妨先来解决本章第一个问题。
学员信息表（stuInfo）和学员成绩表（stuMarks）的数据。
stuNamestuNostuSexstuAgestuSeatstuAddress
张秋丽s25301男181北京海淀
李思文s25303女222河南洛阳
李文才s25302男853地址不详
欧阳俊雄s25304男284新疆
梅超风s25318女235地址不详
ExamNostuNowrittenExamlabExam
s271811s253039058
s271813s253026090
s271816s253018782
问题：查看年龄比“李思文”大的学员，要求显示这些学员的信息。
实现方法一：采用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)的原始数据如下：
stuNamestuNostuSexstuAgestuSeatstuAddress
张秋丽s25301男181北京海淀
李思文s25303女222河南洛阳
李文才s25302男853地址不详
欧阳俊雄s25304男284新疆
梅超风s25318女235地址不详
ExamNostuNowrittenExamLabExam
s271811s253039359
s271813s253026391
s271816s253019083
s271817s253186353
问题：
（1）统计本次考试的缺考情况，结果如图第一个记录集所示。
应到人数实到人数缺考人数
541
姓名学号笔试成绩机试成绩是否通过
张秋丽s253019089是
李思文s253039365是
李文才s253026397是
欧阳俊雄s25304缺考缺考否
梅超风s253186359否
总人数通过人数通过率
5360%
（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
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至253000106@qq.com举报，一经查实，本站将立刻删除。
发布者：PHP中文网，转转请注明出处：https://www.chuangxiangniao.com/p/1850564.html
