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

這個範例只傳回產品行是 R 且製造天數小於 4 天的 Product 資料列。

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. 搭配 SELECT 使用 DISTINCT

下列範例會利用 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 關鍵字之間的差異。兩者都是有效子查詢，擷取產品模型是長袖標誌緊身內衣之各項產品名稱的單一執行個體的，Product 和 ProductModel 資料表的 ProductModelID 號碼相符。

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

下列範例會依照產品識別碼和特殊優惠識別碼，來尋找平均價格和年初至今的銷售總和。

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. 搭配 ORDER BY 使用 GROUP 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 子句。它會依照產品識別碼來分組 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. 搭配 SUM 和 AVG 使用 HAVING

下列範例會依產品識別碼來分組 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

如果您要確定每項產品的計算都至少包含了一千五百個項目，請利用 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 子句

下列範例會尋找依產品識別碼及訂單數量來組織的所有單價小於 $5 之訂單的總價，以及只依產品識別碼來組織的所有小於 $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. 搭配 GROUP BY、COMPUTE 和 ORDER BY 子句使用 SELECT

下列範例只會傳回單價小於 $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. 搭配 CUBE 使用 SELECT 陳述式

下列範例會顯示兩個程式碼範例。第一個範例利用 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 行指出所有客戶總計下了 150 份 Filo Mix 訂單。

結果集的第 11 行指出所有客戶所下的所有產品的訂單總數是 260 份。

結果集的第 12-14 行指出每位客戶的所有產品訂單總數分別是 100、110 和 50。

V. 在含有三個資料行的結果集上使用 CUBE

在下列範例中，SELECT 陳述式會傳回產品模型識別碼、產品名稱及訂單數量。這個範例中的 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 子句會多傳回三種分組。分組的數目可以不只三個，這會隨著資料行中的個別值而不同。

結果集是先依產品模型識別碼，再依產品名稱來分組。

ProductModelID 資料行中的 NULL 代表所有 ProductModels。Name 資料行中的 NULL 代表所有 Products。CUBE 運算子會從 SELECT 陳述式中傳回下列分組資訊：

各項產品模型的訂單數量
各項產品的訂單數量
訂單總數

GROUP BY 子句所參考的每個資料行交互參考 GROUP BY 子句中的所有其他資料行，已重新套用 SUM 彙總。這會在結果集中產生附加的資料列。結果集中傳回的資訊會隨著 GROUP BY 子句中的資料行數目而進行 n 維度成長。

附註：
請確定 GROUP BY 子句之後的資料行彼此之間具備有意義的真實生活關聯性。例如，如果您使用 Name 和 ProductID，CUBE 運算子會傳回不相關的資訊。當在真實生活階層 (如年度銷售和每季銷售) 上使用 CUBE 運算子時，結果集會產生無意義的資料列。使用 ROLLUP 運算子會比較有效。

W. 搭配 CUBE 使用 GROUPING 函數

下列範例會顯示 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. 搭配 GROUP BY、彙總函數和 ROLLUP 來使用 SELECT

下列範例使用包含彙總函數和 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 運算子使用。您可以將這個函數套用在選取清單的某個資料行。這個函數會傳回 1 或 0，隨著資料行是否由 ROLLUP 運算子來分組而不同。

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

下列範例會顯示如何搭配 GROUP BY 子句來使用 OPTION (GROUP) 子句。

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. 搭配 UNION 使用 SELECT INTO

在下列範例中，第二個 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 個資料列。

第三個範例搭配第一個 UNION 來使用 ALL，用括號括住未使用 ALL 的第二個 UNION。第二個 UNION 會先處理，因為它在括號中，且會傳回 5 個資料列，因為並未使用 ALL 選項，複本會移除。這 5 個資料列利用 SELECT 關鍵字，與第一個 UNION ALL 的結果結合起來。這並不會在兩組 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. 搭配 SELECT 使用 DISTINCT

D. 利用 SELECT INTO 來建立資料表

E. 使用相互關聯的子查詢

F. 使用 GROUP BY

G. 搭配多個群組使用 GROUP BY

H. 使用 GROUP BY 和 WHERE

I. 搭配運算式使用 GROUP BY

J. 比較 GROUP BY 和 GROUP BY ALL

K. 搭配 ORDER BY 使用 GROUP BY

L. 使用 HAVING 子句

M. 使用 HAVING 和 GROUP BY

N. 搭配 SUM 和 AVG 使用 HAVING

O. 利用 COMPUTE BY 來計算群組總計

P. 利用不含 BY 的 COMPUTE 來計算總值

Q. 計算所有資料列的計算總和

R. 使用多個 COMPUTE 子句

S. 比較 GROUP BY 和 COMPUTE

T. 搭配 GROUP BY、COMPUTE 和 ORDER BY 子句使用 SELECT

U. 搭配 CUBE 使用 SELECT 陳述式

V. 在含有三個資料行的結果集上使用 CUBE

W. 搭配 CUBE 使用 GROUPING 函數

X. 使用 ROLLUP 運算子

Y. 使用 GROUPING 函數

Z. 搭配 GROUP BY、彙總函數和 ROLLUP 來使用 SELECT

a. 使用 INDEX 最佳化工具提示

b. 使用 OPTION 和 GROUP 提示

c. 使用 UNION 查詢提示

d. 使用簡單 UNION

e. 搭配 UNION 使用 SELECT INTO

f. 搭配 ORDER BY 使用兩個 SELECT 陳述式的 UNION

g. 利用三個 SELECT 陳述式的 UNION 來顯示 ALL 和括號的作用

請參閱

參考

其他資源

說明及資訊

變更歷程記錄

其他資源