SELECT 예(Transact-SQL)
업데이트: 2006년 4월 14일
이 항목에서는 SELECT 문에 대한 사용 예를 보여 줍니다.
1. 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
2. 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
3. SELECT에 DISTINCT 사용
다음 예에서는 DISTINCT를 사용하여 중복된 제목을 검색하지 않도록 합니다.
USE AdventureWorks ;
GO
SELECT DISTINCT Title
FROM HumanResources.Employee
ORDER BY Title ;
GO
4. 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)
5. 상관 하위 쿼리 사용
다음 예에서는 기능상 동일한 쿼리를 보여 주고 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 테이블에서 직원 ID가 일치하는 각 직원의 이름 및 성의 인스턴스 하나를 검색합니다.
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
6. GROUP BY 사용
다음 예에서는 데이터베이스에서 각 판매 주문의 합계를 구합니다.
USE AdventureWorks ;
GO
SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail sod
GROUP BY SalesOrderID
ORDER BY SalesOrderID ;
GO
GROUP BY 절을 사용했으므로 각 판매 주문에 대해 모든 판매의 합계를 포함하는 한 행만 반환됩니다.
7. 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
8. 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
9. 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
10. 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
11. 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
12. HAVING 절 사용
다음 첫 번째 예에서는 HAVING 절에 집계 함수를 사용합니다. SalesOrderDetail 테이블의 행을 제품 ID별로 그룹화하고 평균 주문 수량이 5개 이하인 제품을 제거합니다. 두 번째 예에서는 집계 함수 없이 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
13. 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
14. SUM 및 AVG와 함께 HAVING 사용
다음 예에서는 SalesOrderDetail 테이블을 제품 ID별로 그룹화하며 총 주문액이 $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
15. 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
16. COMPUTE를 BY 없이 사용하여 총합계 계산
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
17. 모든 행의 계산된 합계 계산
다음 예에서는 SELECT 목록에서 세 개의 열만 표시하고 모든 주문 수량과 모든 라인 총합계에 기초하여 결과의 끝에 합계를 제공합니다.
USE AdventureWorks ;
GO
SELECT ProductID, OrderQty, LineTotal
FROM Sales.SalesOrderDetail
COMPUTE SUM(OrderQty), SUM(LineTotal) ;
GO
18. 두 개 이상의 COMPUTE 절 사용
다음 예에서는 단가가 $5 미만인 모든 주문의 가격 합계를 제품 ID 및 주문 수량별로 정렬한 결과와 단가가 $5 미만인 모든 주문의 가격 합계를 제품 ID별로만 정렬한 결과를 반환합니다. 같은 문에 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
19. 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
20. SELECT에 GROUP BY, COMPUTE 및 ORDER BY 절 사용
다음 예에서는 단가가 $5 미만인 주문만 반환한 다음 제품별 라인 총합계와 총합계를 계산합니다. 계산된 모든 열이 SELECT 목록에 나타납니다.
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
21. 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이라는 것을 나타냅니다.
22. 세 열이 있는 결과 집합에서 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 차원의 연산자인지 알 수 있습니다. 두 개의 열이 있는 GROUP BY 절은 CUBE 연산자를 사용할 때 세 종류 이상의 그룹을 반환합니다. 그룹의 개수는 열의 고유한 값에 따라 세 개가 넘을 수도 있습니다.
결과 집합은 제품 모델 ID별로 그룹화된 다음 제품 이름별로 그룹화됩니다.
ProductModelID 열의 NULL은 모든 ProductModels를 나타냅니다. Name 열의 NULL은 모든 Products를 나타냅니다. CUBE 연산자는 하나의 SELECT 문에서 다음 정보 그룹을 반환합니다.
- 각 제품 모델의 주문 수량
- 각 제품의 주문 수량
- 총 주문 수
GROUP BY 절에서 참조되는 각 열은 GROUP BY 절에 있는 다른 모든 열과 상호 참조되었으며 SUM 집계 함수가 다시 적용되었습니다. 이에 따라 결과 집합에 추가 행이 생성됩니다. 결과 집합에 반환되는 정보는 GROUP BY 절에 있는 열의 수에 따라 n 차원으로 증가됩니다.
[!참고] GROUP BY 절의 각 열은 서로 의미 있는 실제 관계가 있어야 합니다. 예를 들어 Name 및 ProductID를 사용하는 경우 CUBE 연산자는 부적절한 정보를 반환합니다. 연간 판매 및 분기별 판매와 같은 실제 계층에서 CUBE 연산자를 사용하면 결과 집합에 의미 없는 행이 생성됩니다. 이런 경우에는 ROLLUP 연산자를 사용하는 것이 더 효율적입니다.
23. 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) 식에서 이러한 열을 생성합니다.
24. 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)
25. GROUPING 함수 사용
다음 예에서는 CubeExample 테이블에 3개의 새 행을 추가합니다. 3개 행은 각각 하나 이상의 열에 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)
26. 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 연산자와 함께 사용할 수 있습니다. SELECT 목록에 있는 열 중 하나의 열에 이 함수를 적용할 수 있습니다. 이 함수는 해당 열이 ROLLUP 연산자를 사용하여 그룹화되었는지 여부에 따라 1 또는 0을 반환합니다.
27. 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
28. 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
29. UNION 쿼리 힌트 사용
다음 예에서는 MERGE UNION 쿼리 힌트를 사용합니다.
USE AdventureWorks ;
GO
SELECT *
FROM HumanResources.Employee e1
UNION
SELECT *
FROM HumanResources.Employee e2
OPTION (MERGE UNION) ;
GO
30. 단순 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
31. UNION과 함께 SELECT INTO 사용
다음 예에서 두 번째 SELECT 문의 INTO 절은 ProductModel 및 Gloves 테이블의 지정된 열에 대해 UNION 연산을 수행한 마지막 결과 집합을 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 ;
32. 두 SELECT 문에서 ORDER BY와 함께 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
33. 3개의 SELECT 문에서 UNION을 사용하여 ALL 및 괄호의 효과 확인
다음 예에서는 UNION을 사용하여 동일한 5개의 데이터 행이 있는 세 테이블의 결과를 결합합니다. 첫 번째 예에서는 UNION ALL을 사용하여 중복 레코드를 보여 주고 15개 행을 모두 반환합니다. 두 번째 예에서는 ALL 없이 UNION을 사용하여 세 SELECT 문의 결합된 결과에서 중복 행을 제거하고 5개 행만 반환합니다.
세 번째 예에서는 첫 번째 UNION에서 ALL을 사용하고 ALL을 사용하지 않는 두 번째 UNION은 괄호로 묶습니다. 두 번째 UNION은 괄호로 묶었기 때문에 먼저 처리되고 ALL 옵션을 사용하지 않아 중복 행이 제거되기 때문에 5개 행을 반환합니다. 이러한 5개 행은 UNION ALL 키워드를 사용하여 첫 번째 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)
관련 자료
분산 쿼리
하위 쿼리 기본 사항
변수 및 매개 변수 사용(데이터베이스 엔진)
도움말 및 정보
변경 내역
| 릴리스 | 내역 |
|---|---|
2006년 4월 14일 |
|