SELECT の例 (Transact-SQL)

  • [アーティクル]

更新 : 2006 年 4 月 14 日

ここでは、SELECT ステートメントの使用例を紹介します。

A. SELECT を使用して行および列を取得する

3 つのプログラム例を次に示します。最初の例では、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 テーブルのすべての行を返します。最初の例では、各製品の売上合計と売上割引を返します。2 番目の例では、製品ごとの合計収入が計算されます。

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_____________________

2 番目の例では、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 番号が一致する各製品名の 1 つのインスタンスを取得する有効なサブクエリの例です。

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 を使用しています。これは、外側のクエリによって値が決まるクエリです。このクエリは、外側のクエリが選択する行に対して 1 回ずつ、繰り返し実行されます。このクエリは、SalesPerson テーブルのボーナス額が 5000.00 で、従業員の ID 番号が Employee テーブルと SalesPerson テーブルで一致する各従業員の姓名のインスタンスを 1 つ取得します。

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 句でも使えます。この例では、表示価格がモデルの平均値の 2 倍以上の製品モデルを検索します。

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

この例では、2 つの相関サブクエリを使って、特定の製品を販売した従業員の名前を検索します。

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 句があるため、各販売注文につき 1 行だけが返され、この行にその販売注文のすべての売上合計が含まれます。

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. 1 つの式と共に 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 の注文についてグループを作成します。

2 番目の例は、すべての注文についてグループを作成します。

条件を満たす行がないグループに対しては、集計値 (平均価格) の列は 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 テーブルの行を製品 ID 別にグループ化し、平均注文数が 5 以下の製品を除外しています。2 番目の例では、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 を使用する

次の例では、1 つの 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 と共に使用する

次の例では、SalesOrderDetail テーブルから、注文合計額が $1000000.00 を超え、かつ平均注文数が 3 未満の製品を製品 ID 別にグループ化します。

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

各製品の集計に最低 1,500 の品目が含まれているようにするには、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 を使った 2 つのプログラム例を示します。最初の例では、1 つの COMPUTE BY に 1 つの集計関数を使用し、2 番目の例では 1 つの COMPUTE BY に 2 つの集計関数を使用しています。

この例では、各種類の製品ごとに、価格が $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 句では 2 つの異なる集約関数を使用します。

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 を使用して総計値を計算する

COMPUTE キーワードは、総計や総数などを作成するために、BY なしで使うことができます。

次の例は、$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. すべての行で計算済みの総計を計算する

次の例では、選択リストに指定されているのは 3 列のみで、すべての注文数量およびすべての行の合計に基づいた合計値が結果の終わりに表示されます。

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

R. 複数の COMPUTE 句を使用する

次の例では、単価が $5 未満のすべての注文の価格合計を製品 ID と注文数量別に検索し、$5 未満のすべての注文の価格合計を製品 ID 別のみで検索します。複数の COMPUTE 句を含めることにより、同じステートメントの中で異なる集計関数を使うことができます。

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 未満のすべての注文の合計を製品の種類別に計算しています。2 番目の例では、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 を使った 2 番目のクエリを次に示します。

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. SELECT ステートメントを CUBE と共に使用する

2 つのプログラム例を次に示します。最初の例では、CUBE 演算子を使用している SELECT ステートメントから結果セットを返しています。CUBE 演算子を使用することにより、ステートメントは 1 行余分に返します。

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 演算子を使用した場合は、同じ結果を返します。

次の例では、CUBE 演算子が結果セットにどのように作用するかを示す CubeExample テーブルと、集計関数 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. 3 つの列を含む結果セットで 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 句内に 2 つの列があると GROUP BY ではさらに 3 つのグループを追加して返します。列の値がまったく異なる場合は、グループの数が 3 より大きくなることがあります。

結果セットは、製品モデル ID 別にグループ化され、さらに製品名別にグループ化されます。

ProductModelID 列の NULL は、すべての ProductModels を表し、Name 列の NULL は、すべての Products を表しています。CUBE 演算子は、1 つの SELECT ステートメントから次の情報のグループを返します。

  • 製品モデルごとの注文数量
  • 製品ごとの注文数量
  • 注文総数

GROUP BY 句で参照される各列は、GROUP BY 句内の他のすべての列と相互参照され、SUM 集計が再適用されています。これにより、結果セットに行が追加されます。結果セットで返される情報は GROUP BY 句内の列の数に従って n 次元まで増加します。

ms187731.note(ja-jp,SQL.90).gifメモ :
GROUP BY 句に続く列は、互いに実用的で意味のある関係になるようにしてください。たとえば、Name と ProductID を使用すると、CUBE 演算子は無関係な情報を返します。このような状態で、年間の販売数や四半期の販売数などの実用的な関係に CUBE 演算子を使用しても、結果セットには無意味な行が生成されるだけです。この場合は、ROLLUP 演算子の方が有効です。

W. GROUPING 関数を CUBE と共に使用する

次の例では、SELECT ステートメントが SUM 集計、GROUP BY 句、および CUBE 演算子を使用する方法を示しています。さらに GROUP BY 句に続く 2 つの列で 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 の値を含む 2 つの列があります。これらの列は、GROUPING(ProductModelID) 式と GROUPING(p.Name) 式で生成されています。

X. ROLLUP 演算子を使用する

2 つのプログラム例を次に示します。最初の例では、製品名、顧客名、合計注文数を取得し、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)

この 2 番目の例では、企業列と部門列で 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 テーブルに新しく 3 行を追加します。3 行それぞれが、1 つ以上の列に 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 であり、すべての値のセットを表しているので、式の値が NULL になるときには、GROUPING 関数は 1 を返します。該当する列が 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 ステートメントを使用すると、ステートメントは 1 つのグループを作成します。このクエリは、ProductModel、ProductModelID、および ProductName の一意な組み合わせごとの合計値を返します。

ProductModel ProductModelID title SUM(qty)

GROUPING 関数は、ROLLUP 演算子、または CUBE 演算子と併用できます。この関数を、選択リスト内の列の 1 つに適用できます。ROLLUP 演算子によって列がグループ化されたかどうかによって 1 または 0 を返します。

a. INDEX オプティマイザ ヒントを使用する

次の例では、INDEX オプティマイザ ヒントの使用方法を 2 とおり示します。最初の例では、オプティマイザで非クラスタ化インデックスを使用し、テーブルから行を取得しています。2 番目の例では、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 を使用する

この例では、2 番目の SELECT ステートメントの INTO 句で、ProductModel および Gloves テーブルの指定された列のユニオンの最終的な結果セットを ProductResults という名前のテーブルに格納することを指定します。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 句を指定した 2 つの SELECT ステートメントで UNION 句を使用する

UNION 句で使用するある種のパラメータの順序には重要な意味があります。次の例では、出力時に列名を変更する 2 つの 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. 3 つの SELECT ステートメントで UNION を使用して、ALL とかっこの効果を示す

次の例では、UNION を使用して 3 つのテーブルのクエリ結果を結合します。これらのテーブルはすべて同じ 5 行のデータで構成されます。最初の例では、UNION ALL を使用して、重複するレコードも含めて 15 行すべてを返します。2 番目の例では、ALL を指定せずに UNION を使用して、3 つの SELECT ステートメントの結果を結合したものから重複する行を削除し、5 行を返します。

3 番目の例では、最初の UNION と共に ALL を使用し、ALL を使用していない 2 番目の UNION をかっこで囲んでいます。2 番目の UNION はかっこで囲まれているので、最初に処理されます。また、ALL オプションを使用せずに重複を削除するので、5 行を返します。これらの 5 行は、UNION ALL キーワードを使用して最初の SELECT の結果と結合されます。これによって 2 組の 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 の参考資料の入手

変更履歴

リリース 履歴

2006 年 4 月 14 日
新しい内容 :
  • HAVING 句の中で LIKE 句を使用する方法を示す、別の例を挿入。