SELECT 示例 (Transact-SQL)

项目
12/15/2008

更新日期： 2006 年 4 月 14 日

本主题将提供使用 SELECT 语句的示例。

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 tempdb ;
IF OBJECT_ID (N'#Bicycles',N'U') IS NOT NULL
DROP TABLE #Bicycles ;
GO
USE AdventureWorks;
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 数据库引擎检查的 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.ContactID WHERE 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

由于使用了 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

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

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

-- 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

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

L. 使用 HAVING 子句

下面的第一个示例显示带聚合函数的 HAVING 子句。该子句按产品 ID 将 SalesOrderDetail 表中的行进行分组并消除那些平均订单数量等于或小于五的产品。第二个示例显示不带聚合函数的 HAVING 子句。

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

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

USE AdventureWorks ;
GO
SELECT SalesOrderID, CarrierTrackingNumber 
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID, CarrierTrackingNumber
HAVING CarrierTrackingNumber LIKE '4BD%'
ORDER BY SalesOrderID ;
GO

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

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

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

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

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

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

增加 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 = 3 ;
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

请参阅

更改历史记录

发布日期	历史记录
2006 年 4 月 14 日	新增内容：插入了不同的示例以显示如何在 HAVING 子句中使用 LIKE。

SELECT 示例 (Transact-SQL)

A. 使用 SELECT 检索行和列

B. 将 SELECT 与列标题和列计算一起使用

C. 将 DISTINCT 与 SELECT 一起使用

D. 使用 SELECT INTO 创建表

E. 使用相关子查询

F. 使用 GROUP BY

G. 对多个组使用 GROUP BY

H. 使用 GROUP BY 和 WHERE

I. 将 GROUP BY 与表达式一起使用

J. 比较 GROUP BY 与 GROUP BY ALL

K. 将 GROUP BY 与 ORDER BY 一起使用

L. 使用 HAVING 子句

M. 使用 HAVING 和 GROUP BY

N. 将 HAVING 与 SUM 和 AVG 一起使用

O. 使用 COMPUTE BY 计算组总计

P. 使用不带 BY 的 COMPUTE 计算总计值

Q. 计算所有行的计算总和

R. 使用多个 COMPUTE 子句

S. 比较 GROUP BY 与 COMPUTE

T. SELECT 与 GROUP BY、COMPUTE 和 ORDER BY 子句一起使用

U. 将 SELECT 语句与 CUBE 一起使用

V. 对包含三列的结果集使用 CUBE

W. 将 GROUPING 函数与 CUBE 一起使用

X. 使用 ROLLUP 运算符

Y. 使用 GROUPING 函数

Z. 将 SELECT 与 GROUP BY、聚合函数和 ROLLUP 一起使用

a. 使用 INDEX 优化器提示

b. 使用 OPTION 和 GROUP 提示

c. 使用 UNION 查询提示

d. 使用简单 UNION

e. 将 SELECT INTO 与 UNION 一起使用

f. 与 ORDER BY 一起使用两个 SELECT 语句的 UNION

g. 使用三个 SELECT 语句的 UNION 来说明 ALL 和括号的作用

请参阅

参考

其他资源

帮助和信息

更改历史记录

其他资源