A. 使用 SELECT 检索行和列
下例显示三个代码示例。第一个代码示例从 pubs 数据库内的 authors 表中返回所有行（没有指定 WHERE 子句）和所有列（使用 *）。
USE pubs
SELECT *
FROM authors
ORDER BY au_lname ASC, au_fname ASC
-- Alternate way.
USE pubs
SELECT authors.*
FROM customers
ORDER BY au_lname ASC, au_fname ASC
下例从 pubs 数据库内的 authors 表中返回所有行（没有指定 WHERE 子句）和列的一个子集（au_lname、au_fname、phone、city、state）。另外，还添加列标题。
USE pubs
SELECT au_fname, au_lname, phone AS Telephone, city, state
FROM authors
ORDER BY au_lname ASC, au_fname ASC
下例只返回居住在加利福尼亚州且不姓 McBadden 的作者列。
USE pubs
SELECT au_fname, au_lname, phone AS Telephone
FROM authors
WHERE state = 'CA' and au_lname <> 'McBadden'
ORDER BY au_lname ASC, au_fname ASC
B. 在列标题和列计算中使用 SELECT
这些示例返回 titles 内的所有行。第一个示例返回本年度截止到现在的销售总额以及应付给每个作者和出版商的金额。在第二个示例中，计算每本书的总收入。
网管bitscn_com
USE pubs
SELECT ytd_sales AS Sales,
authors.au_fname + ' '+ authors.au_lname AS Author,
ToAuthor = (ytd_sales * royalty) / 100,
ToPublisher = ytd_sales - (ytd_sales * royalty) / 100
FROM titles INNER JOIN titleauthor
ON titles.title_id = titleauthor.title_id INNER JOIN authors
ON titleauthor.au_id = authors.au_id
ORDER BY Sales DESC, Author ASC
下面是结果集：
Sales AuthorToAuthorToPublisher
----------- ------------------------- ----------- -----------
22246 Anne Ringer 533916907
22246 Michel DeFrance 533916907
18722 Marjorie Green449314229
15096 Reginald Blotchet-Halls 211312983
8780Cheryl Carson 14047376
4095Abraham Bennet409 3686
4095Akiko Yokomoto409 3686
4095Ann Dull409 3686
网管网www_bitscn_com 4095Burt Gringlesby 409 3686
4095Dean Straight 409 3686
4095Marjorie Green409 3686
4095Michael O'Leary 409 3686
4095Sheryl Hunter 409 3686
4072Johnson White 407 3665
3876Michael O'Leary 387 3489
3876Stearns MacFeather387 3489
3336Charlene Locksley 333 3003
2045Albert Ringer 245 1800
2045Anne Ringer 245 1800
2032Innes del Castillo243 1789
375 Livia Karsen37338
375 Stearns MacFeather37338
375 Sylvia Panteley 37338
111 Albert Ringer 11100
NULLCharlene Locksley NULLNULL
(25 row(s) affected)
网管网www_bitscn_com
下面是用于计算每本书的总收入的查询：
USE pubs
SELECT 'Total income is', price * ytd_sales AS Revenue,
'for', title_id AS Book#
FROM titles
ORDER BY Book# ASC
下面是结果集：
RevenueBook#
--------------- --------------------- ---- ------
Total income is 81859.0500forBU1032
Total income is 46318.2000forBU1111
Total income is 55978.7800forBU2075
Total income is 81859.0500forBU7832
Total income is 40619.6800forMC2222
Total income is 66515.5400forMC3021
Total income is NULLforMC3026
Total income is 201501.0000 forPC1035
Total income is 81900.0000forPC8888
Total income is NULLforPC9999
Total income is 8096.2500 forPS1372
Total income is 22392.7500forPS2091
Total income is 777.0000forPS2106
Total income is 81399.2800forPS3333
网管网www.bitscn.com Total income is 26654.6400forPS7777
Total income is 7856.2500 forTC3218
Total income is 180397.2000 forTC4203
Total income is 61384.0500forTC7777
(18 row(s) affected)
C. 将 DISTINCT 与 SELECT 一起使用
下例使用 DISTINCT 防止检索重复的作者 ID 号：
USE pubs
SELECT DISTINCT au_id
FROM authors
ORDER BY au_id
D. 使用 SELECT INTO 创建表
第一个示例在tempdb 中创建一个名为 #coffeetabletitles 的临时表。为使用该表，始终用下面显示的精确名称（包括井号 (#)）引用它。
USE pubs
DROP TABLE #coffeetabletitles
GO
SET NOCOUNT ON
SELECT * INTO #coffeetabletitles
FROM titles
WHERE price < $20
SET NOCOUNT OFF
SELECT name
FROM tempdb..sysobjects
WHERE name LIKE '#c%'
下面是结果集：
name
------------------------------------------------------------------------
#coffeetabletitles__________________________________________________________________________________________________000000000028
网管联盟bitsCN_com
(1 row(s) affected)
CHECKPOINTing database that was changed.
(12 row(s) affected)
name
------------------------------------------------------------------------
newtitles
(1 row(s) affected)
CHECKPOINTing database that was changed.
第二个示例创建一个名为 newtitles 的永久表。
USE pubs
IF EXISTS (SELECT table_name FROM INFORMATION_SCHEMA.TABLES
WHERE table_name = 'newtitles')
DROP TABLE newtitles
GO
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'
USE pubs
SELECT * INTO newtitles
FROM titles
WHERE price > $25 OR price < $20
SELECT name FROM sysobjects WHERE name LIKE 'new%'
USE master
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'false'
下面是结果集：
name
------------------------------
newtitles
(1 row(s) affected)
中国网管论坛bbs.bitsCN.com
E. 使用相关子查询
下例显示在语义上相当的查询并说明使用 EXISTS 关键字和 IN 关键字的区别。下面是两个示例，显示一个有效子查询检索书名为商业书籍的每个出版商名称，还检索 titles 表和 publishers 表之间相匹配的出版商 ID 号。
USE pubs
SELECT DISTINCT pub_name
FROM publishers
WHERE EXISTS
(SELECT *
FROM titles
WHERE pub_id = publishers.pub_id
AND type = 'business')
-- Or
USE pubs
SELECT distinct pub_name
FROM publishers
WHERE pub_id IN
(SELECT pub_id
FROM titles
WHERE type = 'business')
下例在一个相关（或重复）子查询中使用 IN，该查询的值取决于外部查询。它被重复执行，为外部查询可能选择的每行各执行一次。该查询在 titleauthor 表中检索每个版税为 100% 且作者标识号在 titleauthor 表和 authors 中相匹配的作者的名和姓。
USE pubs
SELECT DISTINCT au_lname, au_fname
FROM authors
WHERE 100 IN
(SELECT royaltyper
FROM titleauthor
WHERE titleauthor.au_id = authors.au_id)
网管论坛bbs_bitsCN_com
不能独立于外部查询对上述语句中的子查询取值。它需要一个 authors.au_id 值，但是该值随 Microsoft? SQL Server? 检查 authors 中的不同行而改变。
相关子查询还可以用于外部查询的 HAVING 子句。下例查找那些预付款最大金额是组平均值两倍以上的书籍类型。
USE pubs
SELECT t1.type
FROM titles t1
GROUP BY t1.type
HAVING MAX(t1.advance) >= ALL
(SELECT 2 * AVG(t2.advance)
FROM titles t2
WHERE t1.type = t2.type)
下例使用两个相关子查询查找作者姓名，这些作者至少参与过一本受欢迎的计算机书籍的创作。
USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE au_id IN
(SELECT au_id
FROM titleauthor
WHERE title_id IN
(SELECT title_id
FROM titles
WHERE type = 'popular_comp'))
F. 使用 GROUP BY
下例在数据库内查找各出版商的本年度截止到现在的销售总额。
USE pubs
SELECT pub_id, SUM(ytd_sales) AS total
FROM titles
GROUP BY pub_id
ORDER BY pub_id
网管网www.bitscn.com
下面是结果集：
pub_id total
------ -----
073628286
087744219
138924941
(3 row(s) affected)
由于使用了 GROUP BY 子句，只为每个出版商各返回一个含有销售总额的行。
G. 对多个组使用 GROUP BY
下例查找按类型和出版商 ID 分组的平均价格和本年度截止到现在的销售总额。
USE pubs
SELECT type, pub_id, AVG(price) AS 'avg', sum(ytd_sales) AS 'sum'
FROM titles
GROUP BY type, pub_id
ORDER BY type, pub_id
下面是结果集：
type pub_id avg sum
------------ ------ --------------------- -----------
business 0736 2.990018722
business 1389 17.3100 12066
mod_cook 0877 11.4900 24278
popular_comp 1389 21.4750 12875
psychology 0736 11.4825 9564
psychology 0877 21.5900 375
trad_cook0877 15.9633 19566
UNDECIDED0877 NULLNULL
网管bitscn_com
(8 row(s) affected)
Warning, null value eliminated from aggregate.
H. 使用 GROUP BY 和 WHERE
下例在只检索预付款多于 $5,000 的行后，将结果分成组。
USE pubs
SELECT type, AVG(price)
FROM titles
WHERE advance > $5000
GROUP BY type
ORDER BY type
下面是结果集：
type
------------ --------------------------
business 2.99
mod_cook 2.99
popular_comp 21.48
psychology 14.30
trad_cook17.97
(5 row(s) affected)
I. 将 GROUP BY 与表达式一起使用
下例按表达式分组。如果表达式不包含聚合函数，则可以按表达式分组。
USE pubs
SELECT AVG(ytd_sales), ytd_sales * royalty
FROM titles
GROUP BY ytd_sales * royalty
ORDER BY ytd_sales * royalty
下面是结果集：
----------- -----------
NULLNULL
111 1110
网管u家u.bitsCN.com
375 3750
203224384
204524540
333633360
387638760
407240720
409540950
8780140480
15096 211344
18722 449328
22246 533904
(13 row(s) affected)
J. 比较 GROUP BY 和 GROUP BY ALL
第一个示例只为要求 10% 版税的书籍生成组。由于没有含 10% 版税的现代烹调书籍，因此结果中没有 mod_cook 类型的组。
第二个示例为所有类型均生成组，包括现代烹调书籍和 UNDECIDED，尽管现代烹调书籍组中没有任何行符合 WHERE 子句中指定的条件。
对于没有符合条件的行的组，容纳聚合值的列（平均价格）为 NULL。
USE pubs
SELECT type, AVG(price)
FROM titles
WHERE royalty = 10
GROUP BY type
ORDER BY type
下面是结果集：
type
------------ --------------------------
business 17.31
popular_comp 20.00
psychology 14.14
网管网www.bitscn.com
trad_cook17.97
(4 row(s) affected)
-- Using GROUP BY ALL
USE pubs
SELECT type, AVG(price)
FROM titles
WHERE royalty = 10
GROUP BY all type
ORDER BY type
下面是结果集：
type
------------ --------------------------
business 17.31
mod_cook NULL
popular_comp 20.00
psychology 14.14
trad_cook17.97
UNDECIDEDNULL
(6 row(s) affected)
K. 将 GROUP BY 与 ORDER BY 一起使用
下例查找各类书籍的平均价格并按平均价格排序结果。
USE pubs
SELECT type, AVG(price)
FROM titles
GROUP BY type
ORDER BY AVG(price)
下面是结果集：
type
------------ --------------------------
UNDECIDEDNULL
mod_cook 11.49
网管网www.bitscn.com
psychology 13.50
business 13.73
trad_cook15.96
popular_comp 21.48
(6 row(s) affected)
L. 使用 HAVING 子句
第一个示例显示带聚合函数的 HAVING 子句。该子句按类型分组 titles 表中的行，并且消除只包含一本书的组。第二个示例显示不带聚合函数的 HAVING 子句。该子句按类型分组 titles 表中的行，并且消除不是以字母 p 开头的类型。
USE pubs
SELECT type
FROM titles
GROUP BY type
HAVING COUNT(*) > 1
ORDER BY type
下面是结果集：
type
------------
business
mod_cook
popular_comp
psychology
trad_cook
(5 row(s) affected)
该查询在 HAVING 子句中使用 LIKE 子句。
USE pubs
SELECT type
FROM titles
GROUP BY type
HAVING type LIKE 'p%'
ORDER BY type
下面是结果集：
type
------------
popular_comp
psychology
(2 row(s) affected)
M. 使用 HAVING 和 GROUP BY
网管u家u.bitscn@com
下例显示在一个 SELECT 语句中使用 GROUP BY、HAVING、WHERE 和 ORDER BY 子句。该语句生成组和汇总值，但却是在消除那些价格低于 $5 的书名后才生成组和汇总值。它还按 pub_id 组织结果。
USE pubs
SELECT pub_id, SUM(advance), AVG(price)
FROM titles
WHERE price >= $5
GROUP BY pub_id
HAVING SUM(advance) > $15000
AND AVG(price) < $20
AND pub_id > '0800'
ORDER BY pub_id
下面是结果集：
pub_id
------ -------------------------- --------------------------
0877 26,000.0017.89
1389 30,000.0018.98
(2 row(s) affected)
N. 将 HAVING 与 SUM 和 AVG 一起使用
下例按出版商分组 titles 表，并只包括那些支付的预付款总额超过 $25,000 且平均书价高于 $15 的出版商的组。
USE pubs
SELECT pub_id, SUM(advance), AVG(price)
FROM titles
GROUP BY pub_id
HAVING SUM(advance) > $25000
AND AVG(price) > $15
网管bitscn_com
若要查看本年度截止到现在的销售额超过 $40,000 的出版商，请使用下面的查询：
USE pubs
SELECT pub_id, total = SUM(ytd_sales)
FROM titles
GROUP BY pub_id
HAVING SUM(ytd_sales) > 40000
如果想确保对每个出版商的计算中至少包含六本书，则使用 HAVING COUNT(*) > 5 消除返回的总数小于六本书的出版商。该查询是这样的：
USE pubs
SELECT pub_id, SUM(ytd_sales) AS total
FROM titles
GROUP BY pub_id
HAVING COUNT(*) > 5
下面是结果集：
pub_id total
------ -----
087744219
138924941
(2 row(s) affected)
使用该语句，返回了两行。消除了 New Moon Books (0736)。
O. 使用 COMPUTE BY 计算组合计
下例使用两个代码示例显示 COMPUTE BY 的用法。第一个代码示例使用一个带一个聚合函数的 COMPUTE BY，第二个代码示例使用一个带两个聚合函数的 COMPUTE BY 函数。
下例先按书籍类型，再按书籍价格计算每类烹调书籍（价格高于 $10）的价格总和。
USE pubs
SELECT type, price
FROM titles
WHERE price > $10
AND type LIKE '%cook'
网管u家u.bitsCN.com
ORDER BY type, price
COMPUTE SUM(price) BY type
下面是结果集：
type price
------------ ---------------------
mod_cook 19.9900
(1 row(s) affected)
sum
---------------------
19.9900
(1 row(s) affected)
type price
------------ ---------------------
trad_cook11.9500
trad_cook14.9900
trad_cook20.9500
(3 row(s) affected)
sum
---------------------
47.8900
(1 row(s) affected)
下例检索所有烹饪书籍的书籍类型、出版商标识号和价格。COMPUTE BY 子句使用两个不同的聚合函数。
USE pubs
SELECT type, pub_id, price
FROM titles
WHERE type LIKE '%cook'
ORDER BY type, pub_id
COMPUTE SUM(price), MAX(pub_id) BY type
下面是结果集：
type pub_id price
------------ ------ ---------------------
mod_cook 0877 19.9900
mod_cook 0877 2.9900
网管u家u.bitsCN.com
(2 row(s) affected)
sum max
--------------------- ----
22.9800 0877
(1 row(s) affected)
type pub_id price
------------ ------ ---------------------
trad_cook0877 20.9500
trad_cook0877 11.9500
trad_cook0877 14.9900
(3 row(s) affected)
sum max
--------------------- ----
47.8900 0877
(1 row(s) affected)
P. 使用不带 BY 的 COMPUTE 计算总计值
可以使用不带 BY 的 COMPUTE 关键字生成总计值、总计数，等等。
该语句查找超过 $20 的所有类型书籍的价格和预付款总计。
USE pubs
SELECT type, price, advance
FROM titles
WHERE price > $20
COMPUTE SUM(price), SUM(advance)
在同一查询内可以使用 COMPUTE BY 和不带 BY 的 COMPUTE。该查询按类型查找价格总和和预付款总和，然后计算所有类型书籍的价格总计和预付款总计。
USE pubs
SELECT type, price, advance
FROM titles
WHERE type LIKE '%cook'
网管u家u.bitscn@com ORDER BY type, price
COMPUTE SUM(price), SUM(advance) BY type
COMPUTE SUM(price), SUM(advance)
下面是结果集：
type price advance
------------ --------------------- ---------------------
mod_cook 2.990015000.0000
mod_cook 19.9900 .0000
(2 row(s) affected)
sum sum
--------------------- ---------------------
22.9800 15000.0000
(1 row(s) affected)
type price advance
------------ --------------------- ---------------------
trad_cook11.9500 4000.0000
trad_cook14.9900 8000.0000
trad_cook20.9500 7000.0000
(3 row(s) affected)
sum sum
--------------------- ---------------------
47.8900 19000.0000
(1 row(s) affected)
网管u家bitscn.net sum sum
--------------------- ---------------------
70.8700 34000.0000
(1 row(s) affected)
Q. 计算所有行上的计算总和
下例只显示选择列表内的三列，并在结果的最后提供基于所有价格和所有预付款的合计。
USE pubs
SELECT type, price, advance
FROM titles
COMPUTE SUM(price), SUM(advance)
下面是结果集：
type price advance
------------ --------------------- ---------------------
business 19.9900 5000.0000
business 11.9500 5000.0000
business 2.990010125.0000
business 19.9900 5000.0000
mod_cook 19.9900 .0000
mod_cook 2.990015000.0000
UNDECIDEDNULLNULL
popular_comp 22.9500 7000.0000
popular_comp 20.0000 8000.0000
popular_comp NULLNULL
psychology 21.5900 7000.0000
网管u家bitscn.net
psychology 10.9500 2275.0000
psychology 7.00006000.0000
psychology 19.9900 2000.0000
psychology 7.99004000.0000
trad_cook20.9500 7000.0000
trad_cook11.9500 4000.0000
trad_cook14.9900 8000.0000
(18 row(s) affected)
sum sum
--------------------- ---------------------
236.260095400.0000
(1 row(s) affected)
Warning, null value eliminated from aggregate.
R. 使用多个 COMPUTE 子句
下例查找所有心理学书籍的价格总和，以及按出版商分类的心理学书籍的价格总和。通过包含一个以上的 COMPUTE BY 子句，可以在同一语句内使用不同的聚合函数。
USE pubs
SELECT type, pub_id, price
FROM titles
WHERE type = 'psychology'
ORDER BY type, pub_id, price
COMPUTE SUM(price) BY type, pub_id
COMPUTE SUM(price) BY type
下面是结果集：
type pub_id price
网管联盟bitsCN_com
------------ ------ ---------------------
psychology 0736 7.0000
psychology 0736 7.9900
psychology 0736 10.9500
psychology 0736 19.9900
(4 row(s) affected)
sum
---------------------
45.9300
(1 row(s) affected)
type pub_id price
------------ ------ ---------------------
psychology 0877 21.5900
(1 row(s) affected)
sum
---------------------
21.5900
(1 row(s) affected)
sum
---------------------
67.5200
(1 row(s) affected)
S. 比较 GROUP BY 与 COMPUTE
第一个示例使用 COMPUTE 子句计算不同类型烹调书籍的价格总和。第二个示例只使用 GROUP BY 生成相同的汇总信息。
USE pubs
-- Using COMPUTE
SELECT type, price
FROM titles
WHERE type like '%cook'
ORDER BY type, price
COMPUTE SUM(price) BY type
下面是结果集：
type price
网管朋友网www_bitscn_net
------------ ---------------------
mod_cook 2.9900
mod_cook 19.9900
(2 row(s) affected)
sum
---------------------
22.9800
(1 row(s) affected)
type price
------------ ---------------------
trad_cook11.9500
trad_cook14.9900
trad_cook20.9500
(3 row(s) affected)
sum
---------------------
47.8900
(1 row(s) affected)
下面是另一个使用 GROUP BY 的查询：
USE pubs
-- Using GROUP BY
SELECT type, SUM(price)
FROM titles
WHERE type LIKE '%cook'
GROUP BY type
ORDER BY type
下面是结果集：
type
------------ ---------------------
mod_cook 22.9800
trad_cook47.8900
(2 row(s) affected)
T. 将 SELECT 与 GROUP BY、COMPUTE 和 ORDER BY 子句一起使用
下例只返回含有本年度截止到现在的当前销售额的行，然后按 type 以递减顺序计算书籍的平均价格和预付款总额。将返回四个数据列，包括截断的书名。所有的计算列都出现在选择列表内。
网管u家u.bitsCN.com USE pubs
SELECT CAST(title AS char(20)) AS title, type, price, advance
FROM titles
WHERE ytd_sales IS NOT NULL
ORDER BY type DESC
COMPUTE AVG(price), SUM(advance) BY type
COMPUTE SUM(price), SUM(advance)
下面是结果集：
titletype price advance
-------------------- ------------ --------------------- ----------------
Onions, Leeks, and G trad_cook20.9500 7000.0000
Fifty Years in Bucki trad_cook11.9500 4000.0000
Sushi, Anyone? trad_cook14.9900 8000.0000
(3 row(s) affected)
avg sum
--------------------- ---------------------
15.9633 19000.0000
(1 row(s) affected
版商为所有作者售出的所有书名的总数量
所有出版商为每位作者售出的每个书名的数量
所有出版商为每位作者售出的所有书名的数量
每个出版商为所有作者售出的每个书名的数量
中国网管联盟bitsCN.com
所有出版商为每位作者售出的每个书名的数量
说明所有出版商、所有书名和所有作者的超聚合比销售总额大，因为许多书的作者不止一位。
模式随关系数的增长而显现出来。报表中的值和 NULL 的模式显示哪些组形成了汇总聚合。有关组的显式信息由 GROUPING 函数提供。
W. 将 GROUPING 函数与 CUBE 一起使用
下例显示 SELECT 语句使用 SUM 聚合、GROUP BY 子句和 CUBE 运算符的方式。它还在 GROUP BY 子句后列出的两列上使用 GROUPING 函数。
USE pubs
SELECT pub_name, GROUPING(pub_name),title, GROUPING(title),
SUM(qty) AS 'qty'
FROM sales INNER JOIN titles
ON sales.title_id = titles.title_id INNER JOIN publishers
ON publishers.pub_id = titles.pub_id
GROUP BY pub_name, title
WITH CUBE
结果集中有两个包含 0 和 1 值的列，这两列由 GROUPING(pub_name) 和 GROUPING(title) 表达式生成。
下面是结果集：
pub_name title qty
-------------------- --- ------------------------- --- -----------
Algodata Infosystems 0 But Is It User Friendly?030
网管联盟bitsCN@com Algodata Infosystems 0 Cooking with Computers: S 025
Algodata Infosystems 0 Secrets of Silicon Valley 050
Algodata Infosystems 0 Straight Talk About Compu 015
Algodata Infosystems 0 The Busy Executive's Data 015
Algodata Infosystems 0 NULL1 135
Binnet & Hardley 0 Computer Phobic AND Non-P 020
Binnet & Hardley 0 Fifty Years in Buckingham 020
......
NULL 1 The Busy Executive's Data 015
NULL 1 The Gourmet Microwave 040
NULL 1 You Can Combat Computer S 035
(36 row(s) affected)
X. 使用 ROLLUP 运算符
下例显示两个代码示例。第一个示例检索产品名称、客户名称和所下的订单总数并使用 ROLLUP 运算符。
USE pubs
SELECT product_name, customer_name, SUM(number_of_orders)
AS 'Sum orders'
网管论坛bbs_bitsCN_com FROM cube_examples
GROUP BY product_name, customer_name
WITH ROLLUP
下面是结果集：
product_name customer_nameSum orders
------------------------------ ------------------------------ ----------
Filo Mix Eastern Connection 40
Filo Mix Romero y tomillo 80
Filo Mix Wilman Kala30
Filo Mix NULL150
IkuraRomero y tomillo 20
IkuraWilman Kala50
IkuraNULL 70
Outback LagerEastern Connection 10
Outback LagerWilman Kala30
网管bitscn_com Outback LagerNULL 40
NULL NULL 260
(11 row(s) affected)
第二个示例显示在公司列和部门列上执行 ROLLUP 运算并合计出雇员总数。
ROLLUP 运算符生成聚合汇总。该运算符用在需要汇总信息但完整的 CUBE 提供的都是无关的数据时，或者用在集内有集的情况中，例如公司内的部门就是集内的集。
USE pubs
CREATE TABLE personnel
(
company_name varchar(20),
department varchar(15),
num_employees int
)
INSERT personnel VALUES ('Du monde entier', 'Finance', 10)
INSERT personnel VALUES ('Du monde entier', 'Engineering', 40)
INSERT personnel VALUES ('Du monde entier', 'Marketing', 40)
INSERT personnel VALUES ('Piccolo und mehr', 'Accounting', 20)
INSERT personnel VALUES ('Piccolo und mehr', 'Personnel', 30)
INSERT personnel VALUES ('Piccolo und mehr', 'Payroll', 40)
在该查询中，除了 ROLLUP 计算结果外，公司名称、部门和公司内所有雇员的总数也成为结果集的一部分。
网管u家www.bitscn.net SELECT company_name, department, SUM(num_employees)
FROM personnel
GROUP BY company_name, department WITH ROLLUP
下面是结果集：
company_name department
-------------------- --------------- -----------
Du monde entierEngineering 40
Du monde entierFinance 10
Du monde entierMarketing 40
Du monde entierNULL90
Piccolo und mehr Accounting20
Piccolo und mehr Payroll 40
Piccolo und mehr Personnel 30
Piccolo und mehr NULL90
NULL NULL180
(9 row(s) affected)
Y. 使用 GROUPING 函数
下例将三个新行添加进 cube_examples 表中。三行中的每行都在一个或多个列中记录 NULL，以便只显示 ROLLUP 函数在分组列中生成值 1。另外，下例修改了在前面的示例中使用的 SELECT 语句。
USE pubs
-- Add first row with a NULL customer name and 0 orders.
网管朋友网www_bitscn_net INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Ikura', NULL, 0)
-- Add second row with a NULL product and NULL customer with real value
-- for orders.
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES (NULL, NULL, 50)
-- Add third row with a NULL product, NULL order amount, but a real
-- customer name.
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES (NULL, 'Wilman Kala', NULL)
SELECT product_name AS Prod, customer_name AS Cust,
SUM(number_of_orders) AS 'Sum Orders',
GROUPING(product_name) AS 'Grp prod_name',
GROUPING(customer_name) AS 'Grp cust_name'
FROM cube_examples
GROUP BY product_name, customer_name
WITH ROLLUP
GROUPING 函数只能与 CUBE 或 ROLLUP 一起使用。表达式取值为 NULL 时，GROUPING 函数返回值 1，因为列值是 NULL 且代表所有值的设置。当相应的列（不管是否是 NULL）不是来自作为语法值的 CUBE 或 ROLLUP 选项时，GROUPING 函数返回值 0。返回值的数据类型为 tinyint。
网管朋友网www_bitscn_net
下面是结果集：
ProdCust Sum OrdersGrp prod_name Grp cust_name
------------- ------------------ ----------- ------------- -------------
NULLNULL 500 0
NULLWilman KalaNULL0 0
NULLNULL 500 1
Filo MixEastern Connection 400 0
Filo MixRomero y tomillo 800 0
Filo MixWilman Kala300 0
Filo MixNULL 150 0 1
Ikura NULL 0 0 0
Ikura Romero y tomillo 200 0
Ikura Wilman Kala500 0
Ikura NULL 700 1
网管有家bitscn.net Outback Lager Eastern Connection 100 0
Outback Lager Wilman Kala300 0
Outback Lager NULL 400 1
NULLNULL 310 1 1
(15 row(s) affected)
Z. 在 SELECT 中使用 GROUP BY、聚合函数和 ROLLUP
下例使用包含聚合函数和 GROUP BY 子句的 SELECT 查询，GROUP BY 子句按顺序先后列出 pub_name、au_lname 和 title。
USE pubs
SELECT pub_name, au_lname, title, SUM(qty) AS 'SUM'
FROM authors INNER JOIN titleauthor
ON authors.au_id = titleauthor.au_id INNER JOIN titles
ON titles.title_id = titleauthor.title_id INNER JOIN publishers
ON publishers.pub_id = titles.pub_id INNER JOIN sales
ON sales.title_id = titles.title_id
GROUP BY pub_name, au_lname, title
WITH ROLLUP
通过使用 ROLLUP 运算符，沿列的列表从右到左移动以创建这些分组。
pub_nameau_lnametitle SUM(qty)
网管u家u.bitsCN.com
pub_nameau_lnameNULLSUM(qty)
pub_nameNULL NULLSUM(qty)
NULL NULL NULLSUM(qty)
NULL 代表该列中的所有值。
如果使用不带 ROLLUP 运算符的 SELECT 语句，该语句则创建单个分组。该查询返回每个 pub_name、au_lname和 title 唯一组合的总和值。
pub_name au_lname title SUM(qty)
将这些示例与在同一查询上使用 CUBE 运算符所创建的分组进行比较。
pub_nameau_lnametitle SUM(qty)
pub_nameau_lnameNULLSUM(qty)
pub_nameNULL NULLSUM(qty)
NULL NULL NULLSUM(qty)
NULL au_lnametitle SUM(qty)
NULL au_lnameNULLSUM(qty)
pub_nameNULL title SUM(qty)
NULL NULL title SUM(qty)
分组对应于结果集中返回的信息。结果集中的 NULL 代表列中的所有值。当列（pub_name、au_lname和title）的顺序和 GROUP BY 子句中列出的顺序一样时，ROLLUP 运算符返回下列数据：
每个出版商为每位作者售出的每个书名的数量
网管联盟bitsCN_com
每个出版商为每位作者售出的所有书名的数量
每个出版商售出的所有书名的数量
所有出版商为所有作者售出的所有书名的总数量
下面是结果集：
pub_nameau_lname titleSUM
----------------- ------------ ------------------------------------ ---
Algodata InfosysBennet The Busy Executive's Database Guide15
Algodata InfosysBennet NULL 15
Algodata InfosysCarson NULL 30
Algodata InfosysDull Secrets of Silicon Valley50
Algodata InfosysDull NULL 50
... ...
New Moon BooksWhiteProlonged Data Deprivation: Four 15
New Moon BooksWhiteNULL 15
New Moon BooksNULL NULL316
NULLNULL NULL791
网管网www_bitscn_com
(49 row(s) affected)
GROUPING 函数可以与 ROLLUP 运算符或 CUBE 运算符一起使用。该函数可以应用于选择列表中的一列。根据该列是否由 ROLLUP 运算符分组，该函数返回 1 或 0。
a. 使用 INDEX 优化程序提示
下例显示使用 INDEX 优化程序提示的两种方式。第一个示例显示强制优化程序使用非聚集索引检索表中的行，第二个示例显示强制使用 0 索引执行表扫描。
-- Use the specifically named INDEX.
USE pubs
SELECT au_lname, au_fname, phone
FROM authors WITH (INDEX(aunmind))
WHERE au_lname = 'Smith'
下面是结果集：
au_lname au_fname phone
-------------------------------------- -------------------- ----------
SmithMeander913 843-0462
(1 row(s) affected)
-- Force a table scan by using INDEX = 0.
USE pubs
SELECT emp_id, fname, lname, hire_date
FROM employee (index = 0)
WHERE hire_date > '10/1/1994'
b. 使用 OPTION 和 GROUP 提示
下例显示如何与 GROUP BY 子句一起使用 OPTION (GROUP) 子句。
网管u家u.bitscn@com
USE pubs
SELECT a.au_fname, a.au_lname, SUBSTRING(t.title, 1, 15)
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id
GROUP BY a.au_lname, a.au_fname, t.title
ORDER BY au_lname ASC, au_fname ASC
OPTION (HASH GROUP, FAST 10)
c. 使用 UNION 查询提示
下例显示使用 MERGE UNION 查询提示。
USE pubs
SELECT *
FROM authors a1
OPTION (MERGE UNION)
SELECT *
FROM authors a2
d. 使用简单 UNION
下例中的结果集包括 Customers 和 SouthAmericanCustomers 这两个表的 ContactName、CompanyName、City 和 Phone 列的内容。
USE Northwind
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'SouthAmericanCustomers')
DROP TABLE SouthAmericanCustomers
GO
-- Create SouthAmericanCustomers table.
SELECT ContactName, CompanyName, City, Phone
INTO SouthAmericanCustomers
FROM Customers
WHERE Country IN ('USA', 'Canada')
网管u家u.bitscn@com
GO
-- Here is the simple union.
USE Northwind
SELECT ContactName, CompanyName, City, Phone
FROM Customers
WHERE Country IN ('USA', 'Canada')
UNION
SELECT ContactName, CompanyName, City, Phone
FROM SouthAmericanCustomers
ORDER BY CompanyName, ContactName ASC
GO
e. 将 SELECT INTO 与 UNION 一起使用
在下例中，第一个 SELECT 语句中的 INTO 子句指定名为 CustomerResults 的表包含由 Customers 和 SouthAmericanCustomers 表中指定列的并集组成的最终结果集。
USE Northwind
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'CustomerResults')
DROP TABLE CustomerResults
GO
USE Northwind
SELECT ContactName, CompanyName, City, Phone INTO CustomerResults
FROM Customers
WHERE Country IN ('USA', 'Canada')
UNION
SELECT ContactName, CompanyName, City, Phone
FROM SouthAmericanCustomers
ORDER BY CompanyName, ContactName ASC
GO
f. 在两个 SELECT 语句中将 UNION 与 ORDER BY 一起使用
网管联盟bitsCN@com
与 UNION 子句一起使用的某些参数的顺序非常重要。下例通过两个 SELECT 语句说明不正确和正确的 UNION 用法，并重命名这些语句输出的列。
/* INCORRECT */
USE Northwind
GO
SELECT City
FROM Customers
ORDER BY Cities
UNION
SELECT Cities = City
FROM SouthAmericanCustomers
GO
/* CORRECT */
USE Northwind
GO
SELECT Cities = City
FROM Customers
UNION
SELECT City
FROM SouthAmericanCustomers
ORDER BY Cities
GO
g. 在三个 SELECT 语句中使用 UNION 以显示 ALL 和圆括号的作用
这些示例使用 UNION 组合三个表的结果，这三个表都有相同的 5 行数据。第一个示例使用 UNION ALL 显示重复的记录并返回全部 15 行。第二个示例使用不带 ALL 的 UNION，从组合的三个 SELECT 语句结果集中删除重复的行。
最后一个示例在第一个 UNION 中使用 ALL，在第二个不带 ALL 的 UNION 中用圆括号将 UNION 括在里面。第二个 UNION 因位于圆括号内而首先得到处理，并且因为没有使用 ALL 选项而返回 5 行且删除重复的行。这 5 行通过 UNION ALL 关键字与第一个 SELECT 的结果组合，且不删除这两个由 5 行组成的结果集之间重复的行。最终结果有 10 行。
网管朋友网www_bitscn_net USE Northwind
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'CustomersOne')
DROP TABLE CustomersOne
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'CustomersTwo')
DROP TABLE CustomersTwo
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'CustomersThree')
DROP TABLE CustomersThree
GO
USE Northwind
GO
SELECT ContactName, CompanyName, City, Phone INTO CustomersOne
FROM Customers
WHERE Country = 'Mexico'
GO
SELECT ContactName, CompanyName, City, Phone INTO CustomersTwo
FROM Customers
WHERE Country = 'Mexico'
GO
SELECT ContactName, CompanyName, City, Phone INTO CustomersThree
FROM Customers
WHERE Country = 'Mexico'
GO
-- Union ALL
SELECT ContactName
FROM CustomersOne
UNION ALL
SELECT ContactName
FROM CustomersTwo
UNION ALL
网管有家www.bitscn.net SELECT ContactName
FROM CustomersThree
GO
USE Northwind
GO
SELECT ContactName
FROM CustomersOne
UNION
SELECT ContactName
FROM CustomersTwo
UNION
SELECT ContactName
FROM CustomersThree
GO
USE Northwind
GO
SELECT ContactName
FROM CustomersOne
UNION ALL
(
SELECT ContactName
FROM CustomersTwo
UNION
SELECT ContactName
FROM CustomersThree
)
GO
评论加载中…