第18学时高级SELECT语句

第18学时高级SELECT语句
上一学时我介绍了S E L E C T语句，我只是粗浅地说明了你可以用它做些什么。实际上你可
以用S E L E C T语句做更多的事情，包括从多个表中提取数据，用函数聚合数据，用子查询建立
查询条件。
本学时的要点包括：
• 快速回顾
• 消除相同行
• 聚合函数
• 关联数据
• 子查询
• SELECT INTO
• UNION
18.1 快速回顾
在深入学习本课程之前，你应该快速回顾一下S E L E C T语句以及S E L E C T语句必要的三个
基本的部分。其中， S E L E C T子句包含S E L E C T关键字和你想得到的列的名称。F R O M子句包
含表的名称，你要从该表提取数据。W H E R E子句用于精简返回给用户的行数。在这个学时，
我将多次用到这些术语，所以记住它们很重要。
18.2 消除相同行
通常，你只想从表中返回唯一的行。例如，这次副总裁要求你来编一份你们公司所出版
的图书的书目表。你一定想写一条能把t y p e列从t i t l e s表取出来的S E L E C T语句。这非常简单，
当你运行该查询时，你将得到1 8行，其中仅有6行才是彼此不同的书目。为了精简，你可以运
行该查询，把数据导入Microsoft Excel，再算出哪个是唯一的。
最简单的方法就是用S E L E C T子句中的D I S T I N C T关键字，告诉SQL Server你只想要彼此
不相同的行。程序清单1 8 - 1显示了D I S T I N C T关键字的用法。
程序清单18-1 DISTINCT用法
在这里，我们所要做的就是在t i t l e s表中寻找对于t y p e列不重复或唯一的行。查询结果如图
1 8 - 1所示。
18.3 聚合函数
聚合函数是用来完成一组值的计算的，并且有一个返回值。大部分情况下，所有这些函
数都忽略传递给它们的空值。这个规则的特例是C O U N T ( )函数。聚合函数有时用GROUP BY
子句，这些我们将在后面的GROUP BY 和H AV I N G部分讨论。使用聚合函数时应遵循一些规
则，它们是：
• 聚合函数允许用在SELECT 语句的S E L E C T列表中。
• 聚合函数允许用在C O M P U T E或COMPUTE BY子句的S E L E C T列表中。
• 聚合函数允许用在本学时后面将要讨论的H AV I N G子句中。
最常用的聚合函数在表1 8 - 1列出。
表18-1 常用聚合函数
函数描述
AV G AV G函数用来返回一组值的平均值。在这组值中，所有的空值是被忽略不计的。使用
该聚合函数有两个选项： A L L和D I S T I N C T。当选用A L L时，SQL Server将把所有的数
据聚合而成为平均值。A L L是缺省值。如果使用D I S T I N C T关键字， SQL Server仅把不
相同的值平均，而无论这个值在表中出现多少次。该函数的语法是AVG(ALL DISTINCT
表达式)
C O U N T C O U N T函数用来返回一组值的个数。通常情况下，这个函数用来统计一个表中的行数，
空值也被计算在内。像AV G函数一样，你也可以有两种选择：A L L和D I S T I N C T。若选择
A L L，SQL Serve统计所有的值。若你选择D I S T I N C T，SQL Server仅统计不相同的值。
该函数的语法是COUNT(ALL DISTINCT表达式* )。这个表达式通常是一个列，若你指
明*，SQL Server 将统计表中所有行。*参数不能和A L L关键字一起用
M A X M A X函数用来在列的一组值中找出最大值。这个函数的语法是M A X (表达式)，表达式
是你想找最大值的列的名称
M I N M I N函数用来在列的一组值中找出最小值。这个函数的语法是M I N (表达式)，表达式
是你想找最小值的列的名称
S U M S U M函数用来返回一组值的总和。S U M函数只能用来对数值进行计算，而且忽略不计
所有的空值。对该函数有两种选择： A L L和D I S T I N C T。若你选择A L L，SQL Serve将对
表中的所有值求和。A L L是缺省值，若选择D I S T I N C T，SQL Server只对不同的值求和。
这个函数的语法是SUM(ALL DISTINCT表达式)，表达式是你要求和的列的名称
来看一下在简单的S E L E C T语句中使用聚合函数的例子。程序清单1 8 - 2在t i t l e s表里用AV G
第1 8学时高级S E L E C T语句1 7 3
下载
图18-1 程序清单1 8 - 1的查询
结果
|
|
|
|
|
|
|
|
函数获得所有书的平均价格。在同一程序清单中，结果紧随查询列出。注意SQL Serve将警告
你它至少从结果中排除了一个空值。
程序清单18-2 AV G函数的使用
程序清单1 8 - 3用COUNT 函数计算t i t l e s表中图书的数量。在同一程序清单中，结果紧随查
询给出。
程序清单18-3 COUNT函数的使用
程序清单1 8 - 4示范了用M A X函数从t i t l e s表里找出最高书价。在同一程序清单中，结果紧
随查询给出。注意SQL Serve将警告你它至少从结果中排除了一个空值。
程序清单18-4 MAX函数的使用
程序清单1 8 - 5示范了用M I N函数从t i t l e s表里找出最低书价。在同一程序清单中，结果紧
随查询给出。注意SQL Serve将警告你它至少从结果中排除了一个空值。
程序清单18-5 MIN函数的使用
1 7 4 SQL Server 7 24学时教程
下载
最后，程序清单1 8 - 6示范了用S U M函数从t i t l e s表里计算出所有图书的总价。在同一程序
清单中，结果紧随查询给出。注意SQL Serve将警告你它至少从结果中排除了一个空值。
程序清单18-6 SUM函数
现在你已经学习了聚合函数的用法，下面将学习使用GROUP BY和H AV I N G子句来产生
更有意义的数据。
GROUP BY和HAVING
G R O U P和H AV I N G子句用于扩展聚合函数的功能。GROUP BY用来指定分组，这些组按
照你希望的那样对输出的行进行划分编排。当你进行任何聚合操作时，该子句都将对组进行
统计计算。在使用GROUP BY时，你必须注意两条规则。首先，列表中所列的任何一项都必
须只返回一个在GROUP BY子句中合法的值。我们看一下GROUP BY子句还有两条规则：
• SELECT列表必须包含一个或多个聚合表达式。
• GROUP BY子句可以选用A L L关键字来指定SQL Server创建所有的组，即使没有任何行
满足搜寻条件。
这些规则听起来很难懂，实际上并不是这样。通常，用来处理A L L关键字的第二条规则
可不使用。你将在S E L E C T语句中正常地按指定列分组。在S E L E C T语句中列出的任何非聚合
列都必须在GROUP BY子句中列出。
例如，设想你是书店的采购主管，你想知道你所买的全部图书按书目分类的平均价格，
可以通过执行程序清单1 8 - 7所示的查询来实现。
程序清单18-7 GROUP BY子句
这个查询将使你获得想要的信息，查询的结果可以在图1 8 - 2中看到。
如今，在大多数书店都会出现这种情况，你要买的书可能来自于不止一个出版商。你决
第1 8学时高级S E L E C T语句1 7 5
下载
定把出版商的标识加到查询中，这样你就可以得到来自不同书目和出版商的图书平均价格，
你可能会通过把p u b i d字段加到S E L E C T列表中来实现，但这会产生以下错误：
这是因为规则所说的S E L E C T列表中任何项只能仅把一个值返回到GROUP BY子句。S Q L
Server 无法判断是否是这种情况。在本例中不是这种情况，所以它将产生一个错误。补救的
方法是如程序清单1 8 - 8所示那样，把新列也加到GROUP BY子句中。
程序清单18-8 多列分组
你所告诉SQL Server的是，你想得到来自不同出版商的按书目分类的图书平均价格，查
询的结果可以在图1 8 - 3中看到。
GROUP BY子句经常和H AV I N G子句结合使用。H AV I N G子句为一个组或一个聚合指定
搜索条件。H AV I N G子句只能和S E L E C T语句一起使用，而且如果不用GROUP BY子句，它的
用法会和W H E R E子句很相似。当你用到GROUP BY子句时，它将帮你限制从聚合函数中返回
的行的数量。程序清单1 8 - 9说明了H AV I N G子句的用法。
程序清单18-9 和H AV I N G子句一起使用的GROUP BY
这次查询将仅返回平均价格超过1 2美元的那些行，查询的结果如图1 8 - 4所示。
1 7 6 SQL Server 7 24学时教程
下载
图18-2 程序清单1 8 - 7的查询
结果
到此为止，在所有你已经看到的语句和查询中，我仅仅提到了从一个表中一次性获取数
据。对完全标准化的表，这几乎是不可能的。
18.4 关联数据
只要参与连结的表之间有一些逻辑关联存在， SQL Server就可以通过使用连结来从多个
表产生关联并返回数据。连结可以在S E L E C T语句中的F O R M子句或W H E R E子句中被指定。
实践中，最好让你的连结保留在F O R M子句里，因为这是S Q L标准指定的。下面是你将用到
的三个基本的连结类型：
• 内连结(INNER JOIN)—内连结是你将要用到的最常见的连结操作。它将会使用一个比
较操作符，像等于(＝)或不等于(＜＞)。一个内连结操作将以两个表中共同的值为基础
第1 8学时高级S E L E C T语句1 7 7
下载
图18-3 程序清单1 8 - 8的查询
结果
图18-4 程序清单1 8 - 9的查询
结果
来匹配两个表中的行。关于该连结的一个例子是，将a u t h o r s表和t i t l e s表中作者标识相同
的行从数据库中检索出来。
• 外连结(OUTER JOIN)—外连接是不常用的一种比较形式。这种类型的连结有三种不
同的方式：右连结、左连结和全连结。
• 左连结(LEFT JOIN)将从连结左边的表中检索所有的行，而不仅仅是那些匹配的行。如
果左边表中的行在右边表中没有相匹配行，检索的结果中对应右边表的列将包含空值。
这种连接可用于返回一张图书馆中所有书的清单。如果这本书被清点过，清点这本书的
人的姓名将出现在右边列中；否则，该字段为空值。
• 右连结(RIGHT JOIN)将检索右边表中所有行和左边表中与右边表相匹配的行。如果在
左边没有与右边相匹配的行，则在该位置返回一个空值。
• 全连结将不管另一边的表是否有匹配行而检索出两表中所有的行。
• 交叉连结(CROSS JOIN)—交叉连结是返回左表所有行并匹配上右表所有行的一种特殊
的连结类型。如果左右两表各有1 0行，SQL Server将返回1 0 0行。交叉连结的结果也被
当作是一种笛卡尔乘积。
现在你已经知道连结是什么了，接下来我们看一些连结的实际应用。在程序清单1 8 - 1 0所
示的第一个连结中，将用内连结找出哪些作者和他们的出版商同住一个州，以及这是哪家出
版公司。
程序清单18-10 INNER JOIN子句
这个查询的结果将告诉你有1 5位作者与他们的出版公司在同一个州，而且他们均为同一
个公司即Algodata Infosystems公司工作。图1 8 - 5显示的是这个查询的结果。
1 7 8 SQL Server 7 24学时教程
下载
图18-5 程序清单1 8 - 1 0的查
询结果
第二个连结如程序清单1 8 - 11所示，它把前面用到的内连结改变为一个左连结。它将返回
所有作者以及那些与作者同在一个州的出版公司。
程序清单1 8 - 11 LEFT JOIN子句
在这个例子中，得到返回的2 3行，其中有在出版公司方面不是空值的1 5行。查询结果如
图1 8 - 6所示。
在程序清单1 8 - 1 2中，你将看到的第三个连结是右连结。这个连结将返回所有出版公司的
名称及那些和他们的出版公司同处一州的作者的姓名。
程序清单18-12 RIGHT JOIN子句
这一次，得到返回的22 行，所有出版商的名称在右边，只有那些和他们的出版公司同居
一州的作者在右边。注意，这些作者与程序清单1 8 - 1 0查询返回的作者相同。查询结果如图
1 8 - 7所示。
在程序清单1 8 - 1 3中，你将要看到的是交叉连结。这个连结将返回a u t h o r s表中所有行并匹
配上p u b l i s h e r s表中的所有行。注意已经去掉了用于产生关联关系的那些列，因为这种连接不
需要它们。
第1 8学时高级S E L E C T语句1 7 9
下载
图18-6 程序清单1 8 - 11的查
询结果
程序清单18-13 CROSS JOIN子句
如图1 8 - 8所示，这个查询一共返回1 8 4行，这是两个表所有行的乘积数。只在少数情况下，
你必须使用这类查询。虽然只在极少数情况下用到它，但你将会为它的存在而高兴。
你已经掌握了S E L E C T语句的主要部分，只剩下最后几点需要讨论了。
18.5 子查询
子查询是从一个以另一个表中你所选的行为基础的表中返回行的过程。许多情况下，子
1 8 0 SQL Server 7 24学时教程
下载
图18-7 程序清单1 8 - 1 2的查
询结果
图18-8 程序清单1 8 - 1 3的查
询结果
查询可以用一些类型的连结代替。使用子查询时必须遵守几条规则，但目前只有其中一条对
你很重要：一个子查询的结果只能包括一个列的名称。
正如我前面提及的，许多涉及子查询的S E L E C T语句能写成某些类型的连结。通常，这会
给你比一个子查询更多的功能。还是用前面的例子，查询哪些作者和他们的出版商位于同一
州。我在这种情况下使用了I N关键字，因为子查询将送回一个包括出版公司所在州的清单。
程序清单18-14 子查询
这个查询结果如图1 8 - 9所示，你可以看到作者及他们所居住的州，但你看不到他们为哪
个出版公司工作。
18.6 SELECT INTO
SELECT INTO语句是S E L E C T语句中很特别的一个类型。这个语句用于获取由一个普通
S E L E C T语句所产生的结果集，并把它放到一个由该语句创建的新表中。这个语句会在数据库
中用和你所指定的列类型和标题创建一个新的表。SELECT INTO是一种不需要重写表的脚本
即可创建一个表的空拷贝的最佳方法，但这个拷贝不包括索引和触发器。你所要做的就是运
行一个W H E R E子句中条件为假的SELECT INTO。你让SELECT INTO产生的表必须是数据库
中不存在的，否则就会产生一个错误。使用这个语句时必须遵守几个规则：
• 执行SELECT INTO语句的人必须在运行该SELECT INTO的数据库中有创建表的许可。
• SELECT INTO是一个无日志记载的过程，所以在成品系统上运行SELECT INTO时必须
十分小心，因为不能通过回滚来复原操作。
• SELECT INTO/BULK COPY数据库选项需要在运行SELECT INTO的数据库中打开。
第1 8学时高级S E L E C T语句1 8 1
下载
图18-9 程序清单1 8 - 1 4的查
询结果
一个例子是创建一个所有作者姓名的清单并把它们放进自己的表中。这可以通过使用
SELECT INTO语句来实现，如程序清单1 8 - 1 5所示。键入这个查询并运行它，看其结果。
程序清单18-15 SELECT INTO子句
正如我以前提及的，创建另一个不含数据的表的拷贝，可运行WHERE 子句中条件为假
的SELECT INTO。如程序清单1 8 - 1 6所示。
程序清单18-16 条件为假的W H E R E子句的SELECT INTO
这个语句将创建一个a u t h o r s表的空表拷贝。
18.7 UNION
最后是UNION 语句。U N I O N语句用于将两个不同的查询结果集相加得到一个结果集。这
个语句最大的限制就是来自两个查询的信息必须有相同的列数和相同的数据类型。
这对于你处理一个表的历史拷贝特别有用的。对公司来说，为每年的销售数据创建一个
新表是很常见的。如果他们想检索过去两年的销售额数据，他们可以运行两个S E L E C T语句并
手工将它们相加起来，他们也可以运行单个的S E L E C T语句并用U N I O N语句连结它。
举个例子，你的公司在s a l e s _ d a t a表中记录每笔销售。每年年底，它运行SELECT INTO
s a l e s _ d a t a _ < y e a r >，< y e a r >是公司的销售年份。这一年的数据被公司存档在表s a l e s _ d a t a _
< y e a r >中，然后他们删除s a l e s d a t a表中的所有数据。当要求你通过查询取出1 9 9 6 ~ 1 9 9 7年的
所有销售信息时，可以用程序清单1 8 - 1 7中的查询实现。
程序清单18-17 UNION 子句
这个结果是你得到单独的一组数据集。
18.8 课时小结
到现在，你已经在S E L E C T语句的世界中经历了一个漫长而曲折的旅行。也许你会说用它
能做不少事。在这一学时中，我主要讲了聚合函数和用GROUP BY与H AV I N G子句生成统计
数据。然后我讲了用连结来关联数据。最后，我介绍了S E L E C T语句的几种特殊用法。在下一
个学时中，我将介绍用于修改数据的语句。
1 8 2 SQL Server 7 24学时教程
下载
18.9 专家答疑
问题：M A X和M I N函数是否只能用于数值型数据？
解答：不是。如果你将M A X和M I N函数用于字符型数据， SQL Server将会根据服务器中
设置的排序次序(通常是字母顺序)在表中找出最大值和最小值。
18.10 课外作业
这些思考题和练习题是供你加深理解用的。答案可以在附录“答案”中找到。
18.10.1 思考题
1) 在S E L E C T语句中用什么关键字能消除重复行？
2) 什么聚合函数能用来对数值类型的列进行求和？
3) 什么聚合函数能用来确定一个表中包含多少行？
4) 在一个包含聚合函数的S E L E C T语句中，GROUP BY子句有哪些用途？
5) 如果只想查看两个连结的表中互相匹配的行，应使用什么类型的连结？
6) 哪种类型的连结的产物被称为笛卡尔乘积？
7) 什么语句可以用来创建一个基于查询结果集的新表？
8) 什么语句可以用来连结两个相似的结果集？
18.10.2 练习题
用内连结编写一条S E L E C T语句以确定哪家书店从出版商那儿定了哪些书。你应该返回订
单编号s t o r i d和书的名称。参与连结的两个表是s a l e s表和t i t l e s表。