您现在的位置： > 技术沙龙 > 数据库 > SQL Server > Transact-SQL 参考 > SELECT 示例
- 相关软件
>SELECT 示例 创建者：webmaster 更新时间：2006-02-16 15:51A. 使用 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 ASCB. 在列标题和列计算中使用 SELECT
这些示例返回 titles 内的所有行。第一个示例返回本年度截止到现在的销售总额以及应付给每个作者和出版商的金额。在第二个示例中，计算每本书的总收入。
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 Author ToAuthor ToPublisher
----------- ------------------------- ----------- -----------
22246 Anne Ringer 5339 16907
22246 Michel DeFrance 5339 16907
18722 Marjorie Green 4493 14229
15096 Reginald Blotchet-Halls 2113 12983
8780 Cheryl Carson 1404 7376
4095 Abraham Bennet 409 3686
4095 Akiko Yokomoto 409 3686
4095 Ann Dull 409 3686
4095 Burt Gringlesby 409 3686
4095 Dean Straight 409 3686
4095 Marjorie Green 409 3686
4095 Michael O'Leary 409 3686
4095 Sheryl Hunter 409 3686
4072 Johnson White 407 3665
3876 Michael O'Leary 387 3489
3876 Stearns MacFeather 387 3489
3336 Charlene Locksley 333 3003
2045 Albert Ringer 245 1800
2045 Anne Ringer 245 1800
2032 Innes del Castillo 243 1789
375 Livia Karsen 37 338
375 Stearns MacFeather 37 338
375 Sylvia Panteley 37 338
111 Albert Ringer 11 100
NULL Charlene Locksley NULL NULL
(25 row(s) affected)下面是用于计算每本书的总收入的查询：
USE pubs
SELECT 'Total income is', price * ytd_sales AS Revenue,
'for', title_id AS Book#
FROM titles
ORDER BY Book# ASC下面是结果集：
Revenue Book#
--------------- --------------------- ---- ------
Total income is 81859.0500 for BU1032
Total income is 46318.2000 for BU1111
Total income is 55978.7800 for BU2075
Total income is 81859.0500 for BU7832
Total income is 40619.6800 for MC2222
Total income is 66515.5400 for MC3021
Total income is NULL for MC3026
Total income is 201501.0000 for PC1035
Total income is 81900.0000 for PC8888
Total income is NULL for PC9999
Total income is 8096.2500 for PS1372
Total income is 22392.7500 for PS2091
Total income is 777.0000 for PS2106
Total income is 81399.2800 for PS3333
Total income is 26654.6400 for PS7777
Total income is 7856.2500 for TC3218
Total income is 180397.2000 for TC4203
Total income is 61384.0500 for TC7777
(18 row(s) affected)C. 将 DISTINCT 与 SELECT 一起使用
下例使用 DISTINCT 防止检索重复的作者 ID 号：
USE pubs
SELECT DISTINCT au_id
FROM authors
ORDER BY au_idD. 使用 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
(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)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')-- OrUSE pubsSELECT distinct pub_nameFROM publishersWHERE 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)不能独立于外部查询对上述语句中的子查询取值。它需要一个 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下面是结果集：
pub_idtotal-----------073628286087744219138924941(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.9900 18722
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_cook 0877 15.9633 19566
UNDECIDED 0877 NULL NULL
(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_cook 17.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下面是结果集：
----------- -----------
NULL NULL
111 1110
375 3750
2032 24384
2045 24540
3336 33360
3876 38760
4072 40720
4095 40950
8780 140480
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
trad_cook 17.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_cook 17.97
UNDECIDED NULL
(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
------------ --------------------------
UNDECIDED NULL
mod_cook 11.49
psychology 13.50
business 13.73
trad_cook 15.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
下例显示在一个 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) > $15000AND AVG(price) < $20
AND pub_id > '0800'ORDER BY pub_id下面是结果集：
pub_id
------ -------------------------- --------------------------
0877 26,000.00 17.89
1389 30,000.00 18.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若要查看本年度截止到现在的销售额超过 $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
------ -----
0877 44219
1389 24941
(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'
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_cook 11.9500
trad_cook 14.9900
trad_cook 20.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
(2 row(s) affected)
sum max
--------------------- ----
22.9800 0877
(1 row(s) affected)
type pub_id price
------------ ------ ---------------------
trad_cook 0877 20.9500
trad_cook 0877 11.9500
trad_cook 0877 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'
ORDER BY type, price
COMPUTE SUM(price), SUM(advance) BY type
COMPUTE SUM(price), SUM(advance)下面是结果集：
type price advance
------------ --------------------- ---------------------
mod_cook 2.9900 15000.0000
mod_cook 19.9900 .0000
(2 row(s) affected)
sum sum
--------------------- ---------------------
22.9800 15000.0000
(1 row(s) affected)
type price advance
------------ --------------------- ---------------------
trad_cook 11.9500 4000.0000
trad_cook 14.9900 8000.0000
trad_cook 20.9500 7000.0000
(3 row(s) affected)
sum sum
--------------------- ---------------------
47.8900 19000.0000
(1 row(s) affected)
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.9900 10125.0000
business 19.9900 5000.0000
mod_cook 19.9900 .0000
mod_cook 2.9900 15000.0000
UNDECIDED NULL NULL
popular_comp 22.9500 7000.0000
popular_comp 20.0000 8000.0000
popular_comp NULL NULL
psychology 21.5900 7000.0000
psychology 10.9500 2275.0000
psychology 7.0000 6000.0000
psychology 19.9900 2000.0000
psychology 7.9900 4000.0000
trad_cook 20.9500 7000.0000
trad_cook 11.9500 4000.0000
trad_cook 14.9900 8000.0000
(18 row(s) affected)
sum sum
--------------------- ---------------------
236.2600 95400.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
------------ ------ ---------------------
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
------------ ---------------------
mod_cook 2.9900
mod_cook 19.9900
(2 row(s) affected)
sum
---------------------
22.9800
(1 row(s) affected)
type price
------------ ---------------------
trad_cook 11.9500
trad_cook 14.9900
trad_cook 20.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_cook 47.8900
(2 row(s) affected)T. 将 SELECT 与 GROUP BY、COMPUTE 和 ORDER BY 子句一起使用
下例只返回含有本年度截止到现在的当前销售额的行，然后按 type 以递减顺序计算书籍的平均价格和预付款总额。将返回四个数据列，包括截断的书名。所有的计算列都出现在选择列表内。
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)下面是结果集：
title type price advance
-------------------- ------------ --------------------- ----------------
Onions, Leeks, and G trad_cook 20.9500 7000.0000
Fifty Years in Bucki trad_cook 11.9500 4000.0000
Sushi, Anyone? trad_cook 14.9900 8000.0000
(3 row(s) affected)
avg sum
--------------------- ---------------------
15.9633 19000.0000
(1 row(s) affected)
title type price advance
-------------------- ------------ --------------------- ----------------
Computer Phobic AND psychology 21.5900 7000.0000
Is Anger the Enemy? psychology 10.9500 2275.0000
Life Without Fear psychology 7.0000 6000.0000
Prolonged Data Depri psychology 19.9900 2000.0000
Emotional Security: psychology 7.9900 4000.0000
(5 row(s) affected)
avg sum
--------------------- ---------------------
13.5040 21275.0000
(1 row(s) affected)
title type price advance
-------------------- ------------ --------------------- ----------------
But Is It User Frien popular_comp 22.9500 7000.0000
Secrets of Silicon V popular_comp 20.0000 8000.0000
(2 row(s) affected)
avg sum
--------------------- ---------------------
21.4750 15000.0000
(1 row(s) affected)
title type price advance
-------------------- ------------ --------------------- ----------------
Silicon Valley Gastr mod_cook 19.9900 .0000
The Gourmet Microwav mod_cook 2.9900 15000.0000
(2 row(s) affected)
avg sum
--------------------- ---------------------
11.4900 15000.0000
(1 row(s) affected)
title type price advance
-------------------- ------------ --------------------- ----------------
The Busy Executive's business 19.9900 5000.0000
Cooking with Compute business 11.9500 5000.0000
You Can Combat Compu business 2.9900 10125.0000
Straight Talk About business 19.9900 5000.0000
(4 row(s) affected)
avg sum
--------------------- ---------------------
13.7300 25125.0000
(1 row(s) affected)
sum sum
--------------------- ---------------------
236.2600 95400.0000
(1 row(s) affected)U. 将 SELECT 语句与 CUBE 一起使用
下例显示两个代码示例。第一个示例使用 CUBE 运算符从 SELECT 语句返回结果集。SELECT 语句包含每本书的书名与销售量之间的一对多关系。通过使用 CUBE 运算符，该语句返回额外的行。
USE pubs
SELECT SUBSTRING(title, 1, 65) AS title, SUM(qty) AS 'qty'
FROM sales INNER JOIN titles
ON sales.title_id = titles.title_id
GROUP BY title
WITH CUBE
ORDER BY title下面是结果集：
title qty
----------------------------------------------------------------- ------
NULL 493
But Is It User Friendly? 30
Computer Phobic AND Non-Phobic Individuals: Behavior Variations 20
Cooking with Computers: Surreptitious Balance Sheets 25
...
The Busy Executive's Database Guide 15
The Gourmet Microwave 40
You Can Combat Computer Stress! 35
(17 row(s) affected)NULL 代表 title 列中的所有值。结果集返回每个书名对应的销售量和所有书名对应的销售总量的值。应用 CUBE 运算符或 ROLLUP 运算符将返回相同的结果。
下例使用 cube_examples 表显示 CUBE 运算符如何影响结果集并使用聚合函数 (SUM)。cube_examples 表包含产品名称、客户名称以及每个客户对某个特定产品下的订单数。
USE pubs
CREATE TABLE cube_examples
(product_name varchar(30) NULL,
customer_name varchar(30) NULL,
number_of_orders int NULL
)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Filo Mix', 'Romero y tomillo', 10)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Outback Lager', 'Wilman Kala', 10)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Filo Mix', 'Romero y tomillo', 20)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Ikura', 'Wilman Kala', 10)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Ikura', 'Romero y tomillo', 10)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Outback Lager', 'Wilman Kala', 20)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Filo Mix', 'Wilman Kala', 30)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Filo Mix', 'Eastern Connection', 40)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Outback Lager', 'Eastern Connection', 10)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Ikura', 'Wilman Kala', 40)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Ikura', 'Romero y tomillo', 10)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Filo Mix', 'Romero y tomillo', 50)首先，发出一个带 GROUP BY 子句和结果集的典型查询。
USE pubs
SELECT product_name, customer_name, SUM(number_of_orders)
FROM cube_examples
GROUP BY product_name, customer_name
ORDER BY product_nameGROUP BY 使结果集在组内形成组。下面是结果集：
product_name customer_name
------------------------------ ------------------------------ ----------
Filo Mix Eastern Connection 40
Filo Mix Romero y tomillo 80
Filo Mix Wilman Kala 30
Ikura Romero y tomillo 20
Ikura Wilman Kala 50
Outback Lager Eastern Connection 10
Outback Lager Wilman Kala 30
(7 row(s) affected)然后，使用 CUBE 运算符发出一个带 GROUP BY 子句的查询。结果集应包括相同的信息以及各 GROUP BY 列的超聚合信息。
USE pubs
SELECT product_name, customer_name, SUM(number_of_orders)
FROM cube_examples
GROUP BY product_name, customer_name
WITH CUBECUBE 运算符的结果集包含上述简单 GROUP BY 结果集的值，并为 GROUP BY 子句中的各行添加超聚合信息。NULL 代表结果集中所有计算出的聚合值。下面是结果集：
product_name customer_name
------------------------------ ------------------------------ ----------
Filo Mix Eastern Connection 40
Filo Mix Romero y tomillo 80
Filo Mix Wilman Kala 30
Filo Mix NULL 150
Ikura Romero y tomillo 20
Ikura Wilman Kala 50
Ikura NULL 70
Outback Lager Eastern Connection 10
Outback Lager Wilman Kala 30
Outback Lager NULL 40
NULL NULL 260
NULL Eastern Connection 50
NULL Romero y tomillo 100
NULL Wilman Kala 110
(14 row(s) affected)结果集的第 4 行表示所有客户对 Filo Mix 总共下了 150 份订单。
结果集的第 11 行表示所有客户对所有产品下的订单总数为 260。
结果集的第 12-14 行表示每个客户对所有产品下的订单总数分别为 100、110 和 50。
V. 在包含三列的结果集上使用 CUBE
下例显示两个代码示例。第一个代码示例生成包含三列的 CUBE 结果集，第二个示例生成包含四列的 CUBE 结果集。
第一个 SELECT 语句返回所售书籍的发行名称、书名和数量。下例中的 GROUP BY 子句包含两个分别称为 pub_name 和 title 的列。在 publishers 和 titles 之间以及 titles 和 sales 之间还存在两个一对多关系。
通过使用 CUBE 运算符，使结果集中包含有关出版商售出的书名数量的更详细信息。NULL 代表书名列中的所有值。
USE pubs
SELECT pub_name, 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下面是结果集：
pub_name title qty
-------------------- ---------------------------------------- ------
Algodata Infosystems But Is It User Friendly? 30
Algodata Infosystems Cooking with Computers: Surreptitious Ba 25
Algodata Infosystems Secrets of Silicon Valley 50
Algodata Infosystems Straight Talk About Computers 15
Algodata Infosystems The Busy Executive's Database Guide 15
Algodata Infosystems NULL 135
Binnet & Hardley Computer Phobic AND Non-Phobic Individu 20
Binnet & Hardley Fifty Years in Buckingham Palace Kitche 20
... ...
NULL Sushi, Anyone? 20
NULL The Busy Executive's Database Guide 15
NULL The Gourmet Microwave 40
NULL You Can Combat Computer Stress! 35
(36 row(s) affected)增加 GROUP BY 子句中的列数将显示 CUBE 运算符是 n 维运算符的原因。使用 CUBE 运算符时，有两列的 GROUP BY 子句将多返回三种分组。根据列中的非重复值，分组的个数可以多于三个。
结果集先按出版商名称，然后按书名分组。右边的列中列出每个出版商售出的每个书名的数量。
title 列中的 NULL 代表所有书名。有关如何区分结果集中特定值和所有值的更多信息，请参见示例 H。CUBE 运算符从一个 SELECT 语句中返回下列几组信息：
- 每个出版商售出的每个书名的数量
- 售出的每个书名的数量
- 每个出版商售出的书名数量
- 所有出版商售出的书名总数
GROUP BY 子句中引用的每列已与 GROUP BY 中的所有其它列交叉引用，并已重新应用 SUM 聚合，这就在结果集中生成附加的行。结果集中返回的信息随 GROUP BY 子句中列数的增长在 n 维方向增长。
说明 请确保在 GROUP BY 子句后列出的列相互之间是有意义的实质关系。例如，如果使用 au_fname 和 au_lname，CUBE 运算符将返回不相关的信息，如名字相同的作者售出的书籍数目。在实质层次结构（如年度销售额和季度销售额）上使用 CUBE 运算符将在结果集中生成无意义的行。使用 ROLLUP 运算符更有效。
在第二个代码示例中，GROUP BY 子句包含由 CUBE 运算符交叉引用的三列。在 publishers 和 authors、authors 和 titles 以及 titles 和 sales 之间存在一对多关系。
使用 CUBE 运算符将返回有关出版商售出的书名数量的更详细信息。
USE pubs
SELECT pub_name, au_lname, title, SUM(qty)
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 CUBE基于 CUBE 运算符返回的交叉引用分组，CUBE 运算符返回下列信息：
- 每个出版商为每位作者售出的每个书名的数量
- 每个出版商为每位作者售出的所有书名的数量
- 每个出版商售出的所有书名的数量
- 所有出版商为所有作者售出的所有书名的总数量
- 所有出版商为每位作者售出的每个书名的数量
- 所有出版商为每位作者售出的所有书名的数量
- 每个出版商为所有作者售出的每个书名的数量
- 所有出版商为每位作者售出的每个书名的数量
说明 所有出版商、所有书名和所有作者的超聚合比销售总额大，因为许多书的作者不止一位。
模式随关系数的增长而显现出来。报表中的值和 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? 0 30
Algodata Infosystems 0 Cooking with Computers: S 0 25
Algodata Infosystems 0 Secrets of Silicon Valley 0 50
Algodata Infosystems 0 Straight Talk About Compu 0 15
Algodata Infosystems 0 The Busy Executive's Data 0 15
Algodata Infosystems 0 NULL 1 135
Binnet & Hardley 0 Computer Phobic AND Non-P 0 20
Binnet & Hardley 0 Fifty Years in Buckingham 0 20
... ...
NULL 1 The Busy Executive's Data 0 15
NULL 1 The Gourmet Microwave 0 40
NULL 1 You Can Combat Computer S 0 35
(36 row(s) affected)X. 使用 ROLLUP 运算符
下例显示两个代码示例。第一个示例检索产品名称、客户名称和所下的订单总数并使用 ROLLUP 运算符。
USE pubs
SELECT product_name, customer_name, SUM(number_of_orders)
AS 'Sum orders'
FROM cube_examples
GROUP BY product_name, customer_name
WITH ROLLUP下面是结果集：
product_name customer_name Sum orders
------------------------------ ------------------------------ ----------
Filo Mix Eastern Connection 40
Filo Mix Romero y tomillo 80
Filo Mix Wilman Kala 30
Filo Mix NULL 150
Ikura Romero y tomillo 20
Ikura Wilman Kala 50
Ikura NULL 70
Outback Lager Eastern Connection 10
Outback Lager Wilman Kala 30
Outback Lager NULL 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 计算结果外，公司名称、部门和公司内所有雇员的总数也成为结果集的一部分。
SELECT company_name, department, SUM(num_employees)
FROM personnel
GROUP BY company_name, department WITH ROLLUP下面是结果集：
company_name department
-------------------- --------------- -----------
Du monde entier Engineering 40
Du monde entier Finance 10
Du monde entier Marketing 40
Du monde entier NULL 90
Piccolo und mehr Accounting 20
Piccolo und mehr Payroll 40
Piccolo und mehr Personnel 30
Piccolo und mehr NULL 90
NULL NULL 180
(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.
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 ROLLUPGROUPING 函数只能与 CUBE 或 ROLLUP 一起使用。表达式取值为 NULL 时，GROUPING 函数返回值 1，因为列值是 NULL 且代表所有值的设置。当相应的列（不管是否是 NULL）不是来自作为语法值的 CUBE 或 ROLLUP 选项时，GROUPING 函数返回值 0。返回值的数据类型为 tinyint。
下面是结果集：
Prod Cust Sum Orders Grp prod_name Grp cust_name
------------- ------------------ ----------- ------------- -------------
NULL NULL 50 0 0
NULL Wilman Kala NULL 0 0
NULL NULL 50 0 1
Filo Mix Eastern Connection 40 0 0
Filo Mix Romero y tomillo 80 0 0
Filo Mix Wilman Kala 30 0 0
Filo Mix NULL 150 0 1
Ikura NULL 0 0 0
Ikura Romero y tomillo 20 0 0
Ikura Wilman Kala 50 0 0
Ikura NULL 70 0 1
Outback Lager Eastern Connection 10 0 0
Outback Lager Wilman Kala 30 0 0
Outback Lager NULL 40 0 1
NULL NULL 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_name au_lname title SUM(qty)
pub_name au_lname NULL SUM(qty)
pub_name NULL NULL SUM(qty)
NULL NULL NULL SUM(qty)NULL 代表该列中的所有值。
如果使用不带 ROLLUP 运算符的 SELECT 语句，该语句则创建单个分组。该查询返回每个 pub_name、au_lname和 title 唯一组合的总和值。
pub_name au_lname title SUM(qty)将这些示例与在同一查询上使用 CUBE 运算符所创建的分组进行比较。
pub_name au_lname title SUM(qty)
pub_name au_lname NULL SUM(qty)
pub_name NULL NULL SUM(qty)
NULL NULL NULL SUM(qty)
NULL au_lname title SUM(qty)
NULL au_lname NULL SUM(qty)
pub_name NULL title SUM(qty)
NULL NULL title SUM(qty)分组对应于结果集中返回的信息。结果集中的 NULL 代表列中的所有值。当列（pub_name、au_lname和title）的顺序和 GROUP BY 子句中列出的顺序一样时，ROLLUP 运算符返回下列数据：
- 每个出版商为每位作者售出的每个书名的数量
- 每个出版商为每位作者售出的所有书名的数量
- 每个出版商售出的所有书名的数量
- 所有出版商为所有作者售出的所有书名的总数量
下面是结果集：
pub_name au_lname title SUM
----------------- ------------ ------------------------------------ ---
Algodata Infosys Bennet The Busy Executive's Database Guide 15
Algodata Infosys Bennet NULL 15
Algodata Infosys Carson NULL 30
Algodata Infosys Dull Secrets of Silicon Valley 50
Algodata Infosys Dull NULL 50
... ...
New Moon Books White Prolonged Data Deprivation: Four 15
New Moon Books White NULL 15
New Moon Books NULL NULL 316
NULL NULL NULL 791
(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
-------------------------------------- -------------------- ----------
Smith Meander 913 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) 子句。
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 a2d. 使用简单 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')
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
GOe. 将 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
GOf. 在两个 SELECT 语句中将 UNION 与 ORDER BY 一起使用
与 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
GOg. 在三个 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 行。
相关文章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
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
本页查看次数: - 每个出版商售出的每个书名的数量