心情

 

A. 使用 SELECT 检索行和列
以下示例显示三个代码示例。第一个代码示例返回 AdventureWorks 数据库的 Product 表中的所有行（未指定 WHERE 子句）和所有列（使用了 *）。

复制代码
USE AdventureWorks ;
GO
SELECT *
FROM Production.Product
ORDER BY Name ASC ;
-- Alternate way.
USE AdventureWorks ;
GO
SELECT p.*
FROM Production.Product p
ORDER BY Name ASC ;
GO


该示例返回 AdventureWorks 数据库的 Product 表的所有行（未指定 WHERE 子句）和列子集（Name、ProductNumber、ListPrice）。此外，还添加了一个列标题。

复制代码
USE AdventureWorks ;
GO
SELECT Name, ProductNumber, ListPrice AS Price
FROM Production.Product
ORDER BY Name ASC ;
GO


该示例仅返回 Product 表中产品系列为 R 且生产天数少于 4 的那些行。

复制代码
USE AdventureWorks ;
GO
SELECT Name, ProductNumber, ListPrice AS Price
FROM Production.Product
WHERE ProductLine = 'R'
AND DaysToManufacture < 4
ORDER BY Name ASC ;
GO


B. 将 SELECT 与列标题和列计算一起使用
下面的示例返回 Product 表中的所有行。第一个示例返回每种产品的总销售额与总折扣。在第二个示例中，计算每种产品的总收入。

复制代码
USE AdventureWorks ;
GO
SELECT p.Name AS ProductName,
NonDiscountSales = (OrderQty * UnitPrice),
Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount)
FROM Production.Product p
INNER JOIN Sales.SalesOrderDetail sod
ON p.ProductID = sod.ProductID
ORDER BY ProductName DESC ;
GO


该查询将计算每个销售订单中每种产品的收入。

复制代码
USE AdventureWorks ;
GO
SELECT 'Total income is', ((OrderQty * UnitPrice) * (1.0 - UnitPriceDiscount)), ' for ',
p.Name AS ProductName
FROM Production.Product p
INNER JOIN Sales.SalesOrderDetail sod
ON p.ProductID = sod.ProductID
ORDER BY ProductName ASC ;
GO


C. 将 DISTINCT 与 SELECT 一起使用
以下示例使用 DISTINCT 以避免检索重复标题。

复制代码
USE AdventureWorks ;
GO
SELECT DISTINCT Title
FROM HumanResources.Employee
ORDER BY Title ;
GO


D. 使用 SELECT INTO 创建表
以下第一个示例将在 tempdb 中创建一个名为 #Bicycles 的临时表。若要使用该表，则必须使用与下面显示的名称完全相同的名称进行引用。这包括数字符号 (#)。

复制代码
USE AdventureWorks ;
GO
DROP TABLE #Bicycles ;
GO
SET NOCOUNT ON

SELECT *
INTO #Bicycles
FROM Production.Product
WHERE ProductNumber LIKE 'BK%'

SET NOCOUNT OFF

SELECT name
FROM tempdb..sysobjects
WHERE name LIKE '#Bicycles%' ;
GO


下面是结果集：

复制代码
name
------------------------------
#Bicycles_____________________


第二个示例创建永久表 NewProducts。

复制代码
USE AdventureWorks ;
GO
IF OBJECT_ID ('dbo.NewProducts', 'U') IS NOT NULL
DROP TABLE dbo.NewProducts ;
GO
ALTER DATABASE AdventureWorks SET RECOVERY BULK_LOGGED ;
GO

SELECT * INTO dbo.NewProducts
FROM Production.Product
WHERE ListPrice > $25
AND ListPrice < $100

SELECT name
FROM sysobjects
WHERE name LIKE 'New%'

USE master ;
GO

ALTER DATABASE AdventureWorks SET RECOVERY FULL ;
GO


下面是结果集：

复制代码
name
------------------------------
NewProducts
(1 row(s) affected)


E. 使用相关子查询
以下示例显示了语义等价的查询并说明了使用 EXISTS 关键字和 IN 关键字的区别。两个都是有效子查询示例，用于检索产品型号为长袖标志运动衫且 ProductModelID 编号在 Product 和 ProductModel 两个表中相匹配的每种产品名称的实例。

复制代码
USE AdventureWorks ;
GO
SELECT DISTINCT Name
FROM Production.Product p
WHERE EXISTS
(SELECT *
FROM Production.ProductModel pm
WHERE p.ProductModelID = pm.ProductModelID
AND pm.Name = 'Long-sleeve logo jersey') ;
GO

-- OR

USE AdventureWorks ;
GO
SELECT DISTINCT Name
FROM Production.Product
WHERE ProductModelID IN
(SELECT ProductModelID
FROM Production.ProductModel
WHERE Name = 'Long-sleeve logo jersey') ;
GO


以下示例在相关或重复子查询中使用 IN。该查询依赖于外部查询来查询其值。该查询为外部查询可能选择的每一行各重复执行一次。该查询检索 SalesPerson 表中奖金为 5000.00 且雇员标识号在 Employee 和 SalesPerson 表中相匹配的每个雇员姓名的实例。

复制代码
USE AdventureWorks ;
GO
SELECT DISTINCT c.LastName, c.FirstName
FROM Person.Contact c JOIN HumanResources.Employee e
ON e.ContactID = c.ContactID WHERE 5000.00 IN
(SELECT Bonus
FROM Sales.SalesPerson sp
WHERE e.EmployeeID = sp.SalesPersonID) ;
GO


该语句中前面的子查询无法独立于外部查询进行计算。它需要使用 Employee.EmployeeID 值，但是该值随着 SQL Server 2005 Database Engine 检查的 Employee 中的不同行而发生改变。

相关子查询还可以用于外部查询的 HAVING 子句。以下示例查找其最高标价高于其平均标价两倍以上的产品型号。

复制代码
USE AdventureWorks
GO
SELECT p1.ProductModelID
FROM Production.Product p1
GROUP BY p1.ProductModelID
HAVING MAX(p1.ListPrice) >= ALL
(SELECT 2 * AVG(p2.ListPrice)
FROM Production.Product p2
WHERE p1.ProductModelID = p2.ProductModelID) ;
GO


此示例使用两个相关子查询查找售出过某种特定产品的雇员的姓名。

复制代码
USE AdventureWorks ;
GO
SELECT DISTINCT c.LastName, c.FirstName
FROM Person.Contact c JOIN HumanResources.Employee e
ON e.ContactID = c.ContactIDWHERE EmployeeID IN
(SELECT SalesPersonID
FROM Sales.SalesOrderHeader
WHERE SalesOrderID IN
(SELECT SalesOrderID
FROM Sales.SalesOrderDetail
WHERE ProductID IN
(SELECT ProductID
FROM Production.Product p
WHERE ProductNumber = 'BK-M68B-42'))) ;
GO


F. 使用 GROUP BY
以下示例查找数据库中各销售订单的总额。

复制代码
USE AdventureWorks ;
GO
SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail sod
GROUP BY SalesOrderID
ORDER BY SalesOrderID ;
GO


下面是结果集：

复制代码
SalesOrderID SubTotal
------------ ----------------------
5001 23.374
5002 2203.7022
5003 880.3484
5004 83.2981
5005 2093.3344 ...
15051 91.468755
15052 271.578384
15053 358.9974 (9179 row(s) affected)


由于使用了 GROUP BY 子句，因此针对每个销售订单只返回一行销售总额。

G. 对多个组使用 GROUP BY
以下示例查找按产品 ID 和特价产品 ID 分组的平均价格和迄今为止的年销售总额。

复制代码
Use AdventureWorks
SELECT ProductID, SpecialOfferID, AVG(UnitPrice) AS 'Average Price',
SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail
GROUP BY ProductID, SpecialOfferID
ORDER BY ProductID
GO


H. 使用 GROUP BY 和 WHERE
以下示例在只检索标价大于 $1000 的行后，将结果进行分组。

复制代码
USE AdventureWorks;
GO
SELECT ProductModelID, AVG(ListPrice) AS 'Average List Price'
FROM Production.Product
WHERE ListPrice > $1000
GROUP BY ProductModelID
ORDER BY ProductModelID ;
GO


下面是结果集：

复制代码
ProductModelID Average List Price
-------------- ------------------------------
5 1357.0500
6 1431.5000
7 1003.9100
19 3378.4382
20 2297.2627
21 1079.9900
25 3578.2700
26 2443.3500
27 1700.9900
28 1457.9900
29 1120.4900
34 2384.0700
35 1214.8500
39 3374.9900
40 1120.4900
41 1700.9900
(16 row(s) affected)


I. 将 GROUP BY 与表达式一起使用
以下示例按表达式进行分组。如果表达式不包含聚合函数，则可以按表达式进行分组。

复制代码
USE AdventureWorks ;
GO
SELECT AVG(OrderQty) AS 'Average Quantity',
NonDiscountSales = (OrderQty * UnitPrice)
FROM Sales.SalesOrderDetail sod
GROUP BY (OrderQty * UnitPrice)
ORDER BY (OrderQty * UnitPrice) DESC ;
GO


下面是结果集：

复制代码
Average Quantity NonDiscountSales
---------------- ------------------------------
26 44629.7904
28 39680.0040
30 36741.3840
21 36047.1384
...
1 2.9940
2 2.7480
1 1.3740
(1434 row(s) affected)


J. 比较 GROUP BY 与 GROUP BY ALL
下面的第一个示例仅为数量 > 10 的订单分组。

第二个示例对所有订单进行分组。

对于不符合条件的行的组，保存聚合值（平均价格）的列为 NULL。

复制代码
USE AdventureWorks ;
GO
SELECT ProductID, AVG(UnitPrice) AS 'Average Price'
FROM Sales.SalesOrderDetail
WHERE OrderQty > 10
GROUP BY ProductID
ORDER BY ProductID ;
GO


下面是结果集：

复制代码
ProductID Average Price
----------- ------------------------------
65 554.0328
67 554.0328
88 777.5928
89 777.5928 ...
1291 4.7870
1298 20.8599
1299 20.8734
(114 row(s) affected)

-- Using GROUP BY ALL
USE AdventureWorks ;
GO
SELECT ProductID, AVG(UnitPrice) AS 'Average Price'
FROM Sales.SalesOrderDetail
WHERE OrderQty > 10
GROUP BY ALL ProductID
ORDER BY ProductID ;
GO


下面是结果集：

复制代码
ProductID Average Price
----------- ------------------------------
3 NULL
5 NULL
8 NULL
...1294 NULL
1298 20.8599
1299 20.8734
Warning: Null value is eliminated by an aggregate or other SET operation.
(292 row(s) affected)


K. 将 GROUP BY 与 ORDER BY 一起使用
以下示例查找每种产品的平均价格并按平均价格将结果排序。

复制代码
USE AdventureWorks ;
GO
SELECT ProductID, AVG(UnitPrice) AS 'Average Price'
FROM Sales.SalesOrderDetail
WHERE OrderQty > 10
GROUP BY ProductID
ORDER BY AVG(UnitPrice) ;
GO


下面是结果集：

复制代码
ProductID Average Price
----------- ------------------------------
1285 1.3740
1277 2.9940
1291 4.7870 ...
169 2429.9928
185 2447.9928
186 2447.9928
(114 row(s) affected)


L. 使用 HAVING 子句
下面的第一个示例显示带聚合函数的 HAVING 子句。该子句按产品 ID 将 SalesOrderDetail 表中的行进行分组并消除那些平均订单数量等于或小于五的产品。第二个示例显示不带聚合函数的 HAVING 子句。该子句按名称将 Product 表中的行生成组，并消除那些未以 Mountain 开头的名称。

复制代码
USE AdventureWorks ;
GO
SELECT ProductID
FROM Sales.SalesOrderDetail
GROUP BY ProductID
HAVING AVG(OrderQty) > 5
ORDER BY ProductID ;
GO


下面是结果集：

复制代码
ProductID
-----------
1223
1224
1225

(3 row(s) affected)


该查询在 HAVING 子句中使用 LIKE 子句。

复制代码
USE AdventureWorks ;
GO
SELECT Name, AVG(ListPrice) AS 'Average List Price'
FROM Production.Product
GROUP BY Name
HAVING Name LIKE 'Mountain%'
ORDER BY Name ;
GO


下面是结果集：

复制代码
Name Average List Price
-------------------------------------------------- ------------------------------
Mountain Bike Socks, L 9.5000
Mountain Bike Socks, M 9.5000
Mountain bottle cage 9.9900 ...
Mountain-500 Yellow, 48 539.9900
Mountain-500 Yellow, 52 539.9900
Mountain-500 Yellow, 56 539.9900
(147 row(s) affected)


M. 使用 HAVING 和 GROUP BY
以下示例显示在一个 SELECT 语句中使用 GROUP BY、HAVING、WHERE 和 ORDER BY 子句。该语句生成组和汇总值（但是组和汇总值是在消除价格超过 $25 且平均订单数量低于 5 的产品之后得出的）。它还按 ProductID 组织其结果。

复制代码
USE AdventureWorks ;
GO
SELECT ProductID
FROM Sales.SalesOrderDetail
WHERE UnitPrice < 25.00
GROUP BY ProductID
HAVING AVG(OrderQty) > 5
ORDER BY ProductID ;
GO


下面是结果集：

复制代码
ProductID
-----------
1223
1224
(3 row(s) affected)


N. 将 HAVING 与 SUM 和 AVG 一起使用
以下示例按产品 ID 将 SalesOrderDetail 表进行分组，结果中仅包含订单总金额超过 $1000000.00 且其平均订单数量少于 3 的产品的组。

复制代码
USE AdventureWorks ;
GO
SELECT ProductID, AVG(OrderQty) AS AverageQuantity, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
GROUP BY ProductID
HAVING SUM(LineTotal) > $1000000.00
AND AVG(OrderQty) < 3 ;
GO


若要查看总销售额超过 $2000000.00 的产品，请使用以下查询：

复制代码
USE AdventureWorks ;
GO
SELECT ProductID, Total = SUM(LineTotal)
FROM Sales.SalesOrderDetail
GROUP BY ProductID
HAVING SUM(LineTotal) > $2000000.00 ;
GO


若要确保在计算中至少为每种产品包含 1500 项，请使用 HAVING COUNT(*) > 1500 消除返回总数少于 1500 售出项的产品。该查询如下所示：

复制代码
USE AdventureWorks ;
GO
SELECT ProductID, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
GROUP BY ProductID
HAVING COUNT(*) > 1500 ;
GO


下面是结果集：

复制代码
ProductID Total
----------- ----------------------
1278 20229.7500000003
1228 54381.0108969958
1299 163045.867224998
1286 167770.886189996
1298 159758.92709
1285 8233.1362399999
1277 28789.8349400036
913 9480.2399999996
912 15444.0499999994
1196 216180.588671996
1293 46619.5800000015
1279 15390.8799999997

(12 row(s) affected)


O. 使用 COMPUTE BY 计算组总计
下面的示例使用两个代码示例说明 COMPUTE BY 的用法。第一个代码示例使用一个 COMPUTE BY 和一个聚合函数，第二个代码示例使用一个 COMPUTE BY 项和两个聚合函数。

该查询针对每个产品类型中其价格低于 $5.00 的产品，计算订单的总和。

复制代码
USE AdventureWorks ;
GO
SELECT ProductID, LineTotal
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
ORDER BY ProductID, LineTotal
COMPUTE SUM(LineTotal) BY ProductID ;
GO


该查询检索单价低于 $5.00 的产品的产品类型及订单总计。COMPUTE BY 子句使用了两个不同的聚合函数。

复制代码
USE AdventureWorks ;
GO
SELECT ProductID, LineTotal
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
ORDER BY ProductID, LineTotal
COMPUTE SUM(LineTotal), MAX(LineTotal) BY ProductID ;
GO


P. 使用不带 BY 的 COMPUTE 计算总计值
可以使用不带 BY 的 COMPUTE 关键字生成总计值、总计数，等等。

以下示例查找价格低于 $2.00 的所有类型产品的价格总计和预付款总计。

复制代码
USE AdventureWorks ;
GO
SELECT ProductID, OrderQty, UnitPrice, LineTotal
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $2.00
COMPUTE SUM(OrderQty), SUM(LineTotal) ;
GO


在同一查询中可以使用 COMPUTE BY 和不带 BY 的 COMPUTE。以下查询按产品类型查找订单数量总和与行总计，然后再计算订单数量总计和行总计。

复制代码
USE AdventureWorks ;
GO
SELECT ProductID, OrderQty, UnitPrice, LineTotal
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
ORDER BY ProductID
COMPUTE SUM(OrderQty), SUM(LineTotal) BY ProductID
COMPUTE SUM(OrderQty), SUM(LineTotal) ;
GO


Q. 针对所有行计算计算总和
以下示例只显示选择列表中的三列，并在最终结果中给出基于所有订单数量和所有行合计的总计。

复制代码
USE AdventureWorks ;
GO
SELECT ProductID, OrderQty, LineTotal
FROM Sales.SalesOrderDetail
COMPUTE SUM(OrderQty), SUM(LineTotal) ;
GO


下面是结果集：

复制代码
ProductID OrderQty LineTotal
----------- -------- ----------------------
1218 4 23.374
527 1 454.3182
558 2 1749.384 ...
1228 17 91.468755
1286 11 271.578384
1299 15 358.9974

sum sum
----------- ----------------------
226751 116011927.11625

ProductID OrderQty LineTotal
----------- -------- ----------------------


R. 使用多个 COMPUTE 子句
以下示例查找按产品 ID 和订单数量组织的单价小于 $5 的所有订单的价格总和，以及只按产品 ID 组织的单价小于 $5 的所有订单的价格总和。通过包含多个 COMPUTE BY 子句，可以在同一语句中使用不同的聚合函数。

复制代码
USE AdventureWorks ;
GO
SELECT ProductID, OrderQty, UnitPrice, LineTotal
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
ORDER BY ProductID, OrderQty, LineTotal
COMPUTE SUM(LineTotal) BY ProductID, OrderQty
COMPUTE SUM(LineTotal) BY ProductID ;
GO


S. 比较 GROUP BY 与 COMPUTE
下面的第一个示例使用 COMPUTE 子句按产品类型计算所有单价低于 $5.00 的所有订单总和。第二个示例只使用 GROUP BY 生成相同的汇总信息。

复制代码
USE AdventureWorks ;
GO
SELECT ProductID, LineTotal
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
ORDER BY ProductID
COMPUTE SUM(LineTotal) BY ProductID ;
GO


下面是使用 GROUP BY 的第二个查询示例。

复制代码
USE AdventureWorks ;
GO
SELECT ProductID, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID
ORDER BY ProductID ;
GO


下面是结果集：

复制代码
ProductID Total
----------- ----------------------
1277 7612.27493999998
1285 925.746240000001
1291 11380.1706
(3 row(s) affected)


T. SELECT 与 GROUP BY、COMPUTE 和 ORDER BY 子句一起使用
以下示例仅返回那些单价低于 $5 的订单，然后再按产品计算行合计，然后计算总计。所有的计算列都出现在选择列表中。

复制代码
USE AdventureWorks ;
GO
SELECT ProductID, OrderQty, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
COMPUTE SUM(SUM(LineTotal)) BY ProductID, OrderQty
COMPUTE SUM(SUM(LineTotal)) ;
GO


U. 将 SELECT 语句与 CUBE 一起使用
以下示例显示了两个代码示例。第一个示例使用 CUBE 运算符从 SELECT 语句返回结果集。由于使用 CUBE 运算符，该语句将返回一个额外的行。

复制代码
USE AdventureWorks ;
GO
SELECT ProductID, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
WITH CUBE
ORDER BY ProductID ;
GO


下面是结果集：

复制代码
ProductID Total
----------- ----------------------
NULL 80752.5517800023
912 15444.0499999994
913 9480.2399999996
914 7425.11999999977
1277 28789.8349400036
1285 8233.1362399999
1291 11380.1706

(7 row(s) affected)


NULL 表示 ProductID 列中的所有值。结果集返回每种产品的售出数量值和所有产品的销售总量值。应用 CUBE 运算符或 ROLLUP 运算符将返回相同的结果。

以下示例使用 CubeExample 表说明 CUBE 运算符如何影响结果集及使用聚合函数 (SUM)。CubeExample 表包含产品名称、客户名称以及每个客户对某个特定产品下的订单数。

复制代码
USE AdventureWorks ;
GO
IF OBJECT_ID ('dbo.CubeExample', 'U') IS NOT NULL
DROP TABLE dbo.CubeExample ;
GO
CREATE TABLE dbo.CubeExample(
ProductName VARCHAR(30) NULL,
CustomerName VARCHAR(30) NULL,
Orders INT NULL
)

INSERT dbo.CubeExample (ProductName, CustomerName, Orders)
VALUES ('Filo Mix', 'Romero y tomillo', 10)
INSERT dbo.CubeExample (ProductName, CustomerName, Orders)
VALUES ('Outback Lager', 'Wilman Kala', 10)
INSERT dbo.CubeExample (ProductName, CustomerName, Orders)
VALUES ('Filo Mix', 'Romero y tomillo', 20)
INSERT dbo.CubeExample (ProductName, CustomerName, Orders)
VALUES ('Ikura', 'Wilman Kala', 10)
INSERT dbo.CubeExample (ProductName, CustomerName, Orders)
VALUES ('Ikura', 'Romero y tomillo', 10)
INSERT dbo.CubeExample (ProductName, CustomerName, Orders)
VALUES ('Outback Lager', 'Wilman Kala', 20)
INSERT dbo.CubeExample (ProductName, CustomerName, Orders)
VALUES ('Filo Mix', 'Wilman Kala', 30)
INSERT dbo.CubeExample (ProductName, CustomerName, Orders)
VALUES ('Filo Mix', 'Eastern Connection', 40)
INSERT dbo.CubeExample (ProductName, CustomerName, Orders)
VALUES ('Outback Lager', 'Eastern Connection', 10)
INSERT dbo.CubeExample (ProductName, CustomerName, Orders)
VALUES ('Ikura', 'Wilman Kala', 40)
INSERT dbo.CubeExample (ProductName, CustomerName, Orders)
VALUES ('Ikura', 'Romero y tomillo', 10)
INSERT dbo.CubeExample (ProductName, CustomerName, Orders)
VALUES ('Filo Mix', 'Romero y tomillo', 50) ;
GO


首先，发出一个带 GROUP BY 子句和结果集的典型查询。

复制代码
USE AdventureWorks ;
GO
SELECT ProductName, CustomerName, SUM(Orders)
FROM CubeExample
GROUP BY ProductName, CustomerName
ORDER BY ProductName ;
GO


GROUP BY 使结果集在组内形成组。

下面是结果集：

复制代码
ProductName CustomerName
------------------------------ ------------------------------ -----------
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 AdventureWorks ;
GO
SELECT ProductName, CustomerName, SUM(Orders)
FROM CubeExample
GROUP BY ProductName, CustomerName
WITH CUBE ;
GO


CUBE 运算符的结果集包含上一个简单 GROUP BY 结果集的值，并添加了 GROUP BY 子句中的每一列的超聚合信息。NULL 表示结果集中为其计算了聚合的所有值。

下面是结果集：

复制代码
ProductName CustomerName
------------------------------ ------------------------------ -----------
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
以下示例中，SELECT 语句返回产品型号 ID、产品名称和订单数量。该示例中的 GROUP BY 子句包含 ProductModelID 和 Name 列。

通过使用 CUBE 运算符，结果集中包含有关产品订单数量和产品型号的更详细信息。NULL 表示标题列中的所有值。

复制代码
USE AdventureWorks ;
GO
SELECT ProductModelID, p.Name AS ProductName, SUM(OrderQty)
FROM Production.Product p
INNER JOIN Sales.SalesOrderDetail sod
ON p.ProductID = sod.ProductID
GROUP BY ProductModelID, p.Name
WITH CUBE ;
GO


下面是结果集：

复制代码
ProductModelID ProductName
-------------- -------------------------------------------------- -----------
NULL LL Mtn Front Wheel 670
NULL NULL 670
1 Classic Vest, L 17
1 Classic Vest, M 2144
1 Classic Vest, S 4173
1 NULL 6334
2 AWC logo cap 6280
2 NULL 6280
3 Full-finger Gloves, L 3450 ...
NULL Women's Mountain shorts, M 800
NULL Women's Mountain shorts, S 3054
NULL Women's tights, L 2194
NULL Women's tights, M 405
NULL Women's tights, S 2162
(705 row(s) affected)


增加 GROUP BY 子句中的列数将说明 CUBE 运算符为 n 维运算符的原因。使用 CUBE 运算符时，具有两个列的 GROUP BY 子句将多返回三种分组。分组的个数可能多于三个，这取决于列中的非重复值。

结果集先按产品型号 ID 进行分组，然后再按产品名称进行分组。

ProductModelID 列中的 NULL 表示所有 ProductModels。Name 列中的 NULL 表示所有 Products。CUBE 运算符由一个 SELECT 语句返回下列几组信息：

每种产品型号的订单数量


每种产品的订单数


订单的总数


GROUP BY 子句中被引用每一列都曾与 GROUP BY 子句中的所有其他列一起被交叉引用，而且 SUM 聚合也被重复应用。这将在结果集中产生额外的行。结果集中返回的信息随 GROUP BY 子句中列数的增长而呈现 n 维增长。

注意：
请确保在 GROUP BY 子句后列出的列相互之间具有有意义的现实性关系。例如，如果使用 Name 和 ProductID，CUBE 运算符将返回不相关的信息。对诸如年度销售额和季度销售额之类的现实性层次结构使用 CUBE 运算符在结果集中生成的行毫无意义。所以使用 ROLLUP 运算符更有效。



W. 将 GROUPING 函数与 CUBE 一起使用
以下示例说明了 SELECT 语句如何使用 SUM 聚合、GROUP BY 子句以及 CUBE 运算符。它还对 GROUP BY 子句后列出的两个列使用 GROUPING 函数。

复制代码
USE AdventureWorks ;
GO
SELECT ProductModelID, GROUPING(ProductModelID), p.Name AS ProductName, GROUPING(p.Name), SUM(OrderQty)
FROM Production.Product p
INNER JOIN Sales.SalesOrderDetail sod
ON p.ProductID = sod.ProductID
GROUP BY ProductModelID, p.Name
WITH CUBE ;
GO


结果集中有两个包含值 0 和 1 的列。这是由 GROUPING(ProductModelID) 和 GROUPING(p.Name) 表达式产生的。

X. 使用 ROLLUP 运算符
以下示例显示了两个代码示例。第一个示例检索产品名称、客户名称和客户所下的订单总数，并且该示例使用了 ROLLUP 运算符。

复制代码
USE AdventureWorks ;
GO
SELECT ProductName, CustomerName, SUM(Orders) AS 'Sum orders'
FROM dbo.CubeExample
GROUP BY ProductName, CustomerName
WITH ROLLUP ;
GO


下面是结果集：

复制代码
ProductName CustomerName 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 AdventureWorks ;
GO
IF OBJECT_ID ('dbo.Personnel', 'U') IS NOT NULL
DROP TABLE dbo.Personnel ;
GO
CREATE TABLE dbo.Personnel
(
CompanyName VARCHAR(20) NOT NULL,
Department VARCHAR(15) NOT NULL,
NumEmployees int NOT NULL
)

INSERT dbo.Personnel VALUES ('Du monde entier', 'Finance', 10)
INSERT dbo.Personnel VALUES ('Du monde entier', 'Engineering', 40)
INSERT dbo.Personnel VALUES ('Du monde entier', 'Marketing', 40)
INSERT dbo.Personnel VALUES ('Piccolo und mehr', 'Accounting', 20)
INSERT dbo.Personnel VALUES ('Piccolo und mehr', 'Personnel', 30)
INSERT dbo.Personnel VALUES ('Piccolo und mehr', 'Payroll', 40) ;
GO


在下列查询中，除了 ROLLUP 计算值外，公司名称、部门和公司内所有雇员的总数也成为结果集的一部分。

复制代码
USE AdventureWorks ;
GO
SELECT CompanyName, Department, SUM(NumEmployees)
FROM dbo.Personnel
GROUP BY CompanyName, Department WITH ROLLUP ;
GO


下面是结果集：

复制代码
CompanyName 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 函数
以下示例向 CubeExample 表中添加三个新行。三行中的每行都在一列或多列中记录 NULL，以便说明只有 ROLLUP 函数在分组列中生成值 1。此外，该示例还修改了上一个示例中使用的 SELECT 语句。

复制代码
USE AdventureWorks ;
GO
-- Add first row with a NULL customer name and 0 orders.
INSERT dbo.CubeExample (ProductName, CustomerName, Orders)
VALUES ('Ikura', NULL, 0)

-- Add second row with a NULL product and NULL customer with real value
-- for orders.
INSERT dbo.CubeExample (ProductName, CustomerName, Orders)
VALUES (NULL, NULL, 50)

-- Add third row with a NULL product, NULL order amount, but a real
-- customer name.
INSERT dbo.CubeExample (ProductName, CustomerName, Orders)
VALUES (NULL, 'Wilman Kala', NULL)

SELECT ProductName AS Prod, CustomerName AS Cust,
SUM(Orders) AS 'Sum Orders',
GROUPING(ProductName) AS 'Group ProductName',
GROUPING(CustomerName) AS 'Group CustomerName'
FROM CubeExample
GROUP BY ProductName, CustomerName
WITH ROLLUP ;
GO


GROUPING 函数只能与 CUBE 或 ROLLUP 一起使用。表达式取值为 NULL 时，GROUPING 函数返回值 1，因为该列值是 NULL 且表示所有值的集合。当相应的列（不管是不是 NULL）不是来自 CUBE 或 ROLLUP 选项的语法值时，GROUPING 函数返回值 0。返回值的数据类型为 tinyint。

下面是结果集：

复制代码
Prod Cust Sum Orders Group ProductName Group CustomerName
------------------------------ ------------------------------ ----------- ----------------- ------------------
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
Warning: Null value is eliminated by an aggregate or other SET operation.
(15 row(s) affected)


Z. 将 SELECT 与 GROUP BY、聚合函数和 ROLLUP 一起使用
以下示例使用了一个包含聚合函数和 GROUP BY 子句的 SELECT 查询。

复制代码
USE AdventureWorks ;
GO
SELECT pm.Name AS ProductModel, p.Name AS ProductName, SUM(OrderQty)
FROM Production.ProductModel pm
INNER JOIN Production.Product p
ON pm.ProductModelID = p.ProductModelID
INNER JOIN Sales.SalesOrderDetail sod
ON p.ProductID = sod.ProductID
GROUP BY pm.Name, p.Name
WITH ROLLUP ;
GO


在结果集中，NULL 表示该列的所有值。

如果使用不带 ROLLUP 运算符的 SELECT 语句，则该语句创建单个分组。该查询为 ProductModel、ProductModelID 和 ProductName 的每个唯一组合返回一个总和值：

ProductModel ProductModelID title SUM(qty)

GROUPING 函数可以与 ROLLUP 运算符或 CUBE 运算符一起使用。该函数可以应用于选择列表中的一列。根据该列是否由 ROLLUP 运算符分组，该函数返回 1 或 0。

a. 使用 INDEX 优化器提示
以下示例说明了使用 INDEX 优化器提示的两种方式。第一个示例说明如何强制优化器使用非聚集索引检索表中的行，第二个示例使用索引 0 强制执行表扫描。

复制代码
-- Use the specifically named INDEX.
USE AdventureWorks ;
GO
SELECT c.FirstName, c.LastName, e.Title
FROM HumanResources.Employee e WITH (INDEX(IX_Employee_ManagerID))
JOIN Person.Contact c on e.ContactID = c.ContactID
WHERE ManagerID = 2 ;
GO

-- Force a table scan by using INDEX = 0.
USE AdventureWorks ;
GO
SELECT c.LastName, c.FirstName, e.Title
FROM HumanResources.Employee e WITH (INDEX = 0) JOIN Person.Contact c
ON e.ContactID = c.ContactID
WHERE LastName = 'Johnson' ;
GO


b. 使用 OPTION 和 GROUP 提示
以下示例说明了如何将 OPTION (GROUP) 子句与 GROUP BY 子句一起使用。

复制代码
USE AdventureWorks ;
GO
SELECT ProductID, OrderQty, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (HASH GROUP, FAST 10) ;
GO


c. 使用 UNION 查询提示
以下示例使用 MERGE UNION 查询提示。

复制代码
USE AdventureWorks ;
GO
SELECT *
FROM HumanResources.Employee e1
UNION
SELECT *
FROM HumanResources.Employee e2
OPTION (MERGE UNION) ;
GO


d. 使用简单 UNION
在以下示例中，结果集同时包含 ProductModel 和 Gloves 表中的 ProductModelID 与 Name 列中的内容。

复制代码
USE AdventureWorks ;
GO
IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL
DROP TABLE dbo.Gloves ;
GO
-- Create Gloves table.
SELECT ProductModelID, Name
INTO dbo.Gloves
FROM Production.ProductModel
WHERE ProductModelID IN (3, 4) ;
GO

-- Here is the simple union.
USE AdventureWorks ;
GO
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3, 4)
UNION
SELECT ProductModelID, Name
FROM dbo.Gloves
ORDER BY Name ;
GO


e. 将 SELECT INTO 与 UNION 一起使用
在以下示例中，第二个 SELECT 语句中的 INTO 子句指定名为 ProductResults 的表保存 ProductModel 和 Gloves 表中的指定列的并集（最终结果集）。注意，Gloves 表是由第一个 SELECT 语句创建的。

复制代码
USE AdventureWorks ;
GO
IF OBJECT_ID ('dbo.ProductResults', 'U') IS NOT NULL
DROP TABLE dbo.ProductResults ;
GO
IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL
DROP TABLE dbo.Gloves ;
GO
-- Create Gloves table.
SELECT ProductModelID, Name
INTO dbo.Gloves
FROM Production.ProductModel
WHERE ProductModelID IN (3, 4) ;
GO

USE AdventureWorks ;
GO
SELECT ProductModelID, Name
INTO ProductResults
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3, 4)
UNION
SELECT ProductModelID, Name
FROM dbo.Gloves ;
GO

SELECT *
FROM dbo.ProductResults ;


f. 与 ORDER BY 一起使用两个 SELECT 语句的 UNION
在 UNION 子句中使用的某些参数的顺序非常重要。以下示例通过两个 SELECT 语句说明 UNION 的错误用法和正确用法（在这两个语句的输出中将重命名一个列）。

复制代码
USE AdventureWorks ;
GO
IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL
DROP TABLE dbo.Gloves ;
GO
-- Create Gloves table.
SELECT ProductModelID, Name
INTO dbo.Gloves
FROM Production.ProductModel
WHERE ProductModelID IN (3, 4) ;
GO

/* INCORRECT */
USE AdventureWorks ;
GO
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3, 4)
ORDER BY Name
UNION
SELECT ProductModelID, Name
FROM dbo.Gloves ;
GO

/* CORRECT */
USE AdventureWorks ;
GO
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3, 4)
UNION
SELECT ProductModelID, Name
FROM dbo.Gloves
ORDER BY Name ;
GO


g. 使用三个 SELECT 语句的 UNION 来说明 ALL 和括号的作用
下列示例使用 UNION 来组合具有相同 5 行数据的三个表的结果。第一个示例使用 UNION ALL 显示重复记录，返回所有的 15 行。第二个示例使用不带 ALL 的 UNION，删除三个 SELECT 语句的组合结果中的重复行，返回 5 行。

第三个示例将 ALL 用于第一个 UNION，并用括号将第二个没有使用 ALL 的 UNION 括起来。第二个 UNION 因位于括号内而首先得到处理，并且因为没有使用 ALL 选项，所以重复行被删除而返回 5 行。通过使用 UNION ALL 关键字将这 5 行与第一个 SELECT 的结果组合在一起。这不会删除两个 5 行结果集之间的重复行。最终结果有 10 行。

复制代码
USE AdventureWorks ;
GO
IF OBJECT_ID ('EmployeeOne', 'U') IS NOT NULL
DROP TABLE EmployeeOne ;
GO
IF OBJECT_ID ('EmployeeTwo', 'U') IS NOT NULL
DROP TABLE EmployeeTwo ;
GO
IF OBJECT_ID ('EmployeeThree', 'U') IS NOT NULL
DROP TABLE EmployeeThree ;
GO

SELECT c.LastName, c.FirstName, e.Title
INTO EmployeeOne
FROM Person.Contact c JOIN HumanResources.Employee e
ON e.ContactID = c.ContactID
WHERE ManagerID = 66 ;
GO
SELECT c.LastName, c.FirstName, e.Title
INTO EmployeeTwo
FROM Person.Contact c JOIN HumanResources.Employee e
ON e.ContactID = c.ContactID
WHERE ManagerID = 66 ;
GO
SELECT c.LastName, c.FirstName, e.Title
INTO EmployeeThree
FROM Person.Contact c JOIN HumanResources.Employee e
ON e.ContactID = c.ContactID
WHERE ManagerID = 66 ;
GO
-- Union ALL
SELECT LastName, FirstName
FROM EmployeeOne
UNION ALL
SELECT LastName, FirstName
FROM EmployeeTwo
UNION ALL
SELECT LastName, FirstName
FROM EmployeeThree ;
GO

SELECT LastName, FirstName
FROM EmployeeOne
UNION
SELECT LastName, FirstName
FROM EmployeeTwo
UNION
SELECT LastName, FirstName
FROM EmployeeThree ;
GO

SELECT LastName, FirstName
FROM EmployeeOne
UNION ALL
(
SELECT LastName, FirstName
FROM EmployeeTwo
UNION
SELECT LastName, FirstName
FROM EmployeeThree
) ;
GO


请参阅
参考
CREATE TRIGGER (Transact-SQL)
CREATE VIEW (Transact-SQL)
DELETE (Transact-SQL)
EXECUTE (Transact-SQL)
表达式（Transact-SQL)
INSERT (Transact-SQL)
LIKE (Transact-SQL)
UNION (Transact-SQL)
EXCEPT 和 INTERSECT (Transact-SQL)
UPDATE (Transact-SQL)
WHERE (Transact-SQL)

其他资源
分布式查询
子查询基础知识
使用变量和参数（数据库引擎）


帮助和信息
获取 SQL Server 2005 帮助文档反馈
Microsoft 非常重视您的反馈。若要对该主题分级并将对该主题的反馈发送到文档编写组，请单击一个分级，再单击“发送反馈”。要想获得有关支持方面的问题的协助，请参阅包含在该产品中的技术支持信息。
不好 1
2
3
4
5
出色
若要将反馈通过电子邮件发送给 Microsoft，请单击此处：
(C) 2005 Microsoft Corporation。保留所有权利。