Примеры использования инструкции SELECT (Transact-SQL)
Изменения: 14 апреля 2006 г.
В этом разделе приведены примеры применения инструкции SELECT.
А. Использование инструкции SELECT для получения строк и столбцов
В следующем примере приведены три примера кода. В ходе выполнения первого примера кода возвращаются все строки (предложение WHERE не указано), а также все столбцы (используется звездочка, *) таблицы Product базы данных AdventureWorks.
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
В ходе выполнения данного примера кода происходит выдача всех строк (предложение WHERE не задано) и подмножества столбцов (Name, ProductNumber, ListPrice) таблицы Product базы данных AdventureWorks. Дополнительно выведено название столбца.
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
Б. Использование инструкции 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
B. Совместное использование DISTINCT и SELECT
В приведенном ниже примере для предотвращения получения повторяющихся заголовков используется оператор DISTINCT.
USE AdventureWorks ;
GO
SELECT DISTINCT Title
FROM HumanResources.Employee
ORDER BY Title ;
GO
Г. Создание таблиц с помощью инструкции 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)
Д. Использование коррелированных запросов
В следующем примере представлены семантически эквивалентные запросы, а также показаны различия в использовании ключевых слов EXISTS и IN. В обоих примерах приведены допустимые вложенные запросы, извлекающие по одному экземпляру продукции каждого наименования, для которых модель продукта — «long sleeve logo jersey» (кофта с длинными рукавами, с эмблемой), а значения столбцов 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, однако в процессе обработки строк Employee компонентом SQL Server 2005 Database Engine указанное значение меняется.
Коррелированный вложенный запрос также может использоваться в предложении 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
Е. Использование GROUP BY
В следующем примере находится общий объем продаж для каждого заказа в базе данных.
USE AdventureWorks ;
GO
SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail sod
GROUP BY SalesOrderID
ORDER BY SalesOrderID ;
GO
Так как в запросе используется предложение GROUP BY, то для каждого заказа выводится только одна строка, содержащая общий объем продаж.
Ж. Использование 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
З. Использование 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
И. Использование 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
К. Сравнение 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
Л. Использование 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
М. Использование предложения 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
Н. Совместное использование HAVING и GROUP BY
В следующем примере показано использование предложений GROUP BY, HAVING, WHERE и ORDER BY в одной инструкции SELECT. В результате его выполнения сначала удаляются строки, соответствующие продуктам с ценами выше $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
О. Совместное использование HAVING, SUM и AVG
В следующем примере производится группировка строк таблицы 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
П. Вычисление общей суммы значений с помощью 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
Р. Вычисление общих значений с помощью оператора 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 и оператор COMPUTE без ключевого слова BY могут использоваться одновременно в одном запросе. В следующем запросе вычисляется объем продаж и суммы для каждого продукта в отдельности, а затем находится общее количество продаж и вырученная сумма.
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
С. Вычисление сумм по всем строкам
В следующем примере в списке выборки выводятся только три столбца, а затем вычисляются общие показатели, основанные на всех заказах во всех строках.
USE AdventureWorks ;
GO
SELECT ProductID, OrderQty, LineTotal
FROM Sales.SalesOrderDetail
COMPUTE SUM(OrderQty), SUM(LineTotal) ;
GO
Т. Использование нескольких предложений 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
У. Сравнение GROUP BY и COMPUTE
В первом из представленных ниже примеров для вычисления суммы всех заказов на продукты, цена которых не превышает $5.00, и группировки их по типу продукта используется предложение COMPUTE. Во втором примере выдача тех же сводных данных происходит с помощью оператора 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
Ф. Использование 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
Х. Совместное использование инструкции 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 результат не изменяется.
В следующем примере для пояснения влияния оператора 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)
Затем аналогичный запрос, содержащий предложение GROUP BY, создается с использованием оператора CUBE. Результирующий набор, кроме информации, полученной в примере выше, также должен содержать статистические данные по каждому столбцу, полученному с помощью 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)
В четвертой строке результирующего набора содержатся сведения о том, что совокупное количество заказов на Filo Mix для всех клиентов составляет 150.
В строке 11 результирующего набора содержатся сведения о том, что совокупное количество заказов всех клиентов на все виды продукции составляет 260.
Строки 12–14 результирующего набора содержат сведения о том, что совокупное количество заказов на все виды продукции для каждого заказчика в отдельности составляет 100, 110 и 50 соответственно.
Ц. Применение оператора 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 возвращает три дополнительных вида группирования. В зависимости от конкретных значений элементов столбцов, количество группирований может увеличиваться.
Результирующий набор сначала группируется по идентификатору модели продукта, а затем по имени продукта.
Значение NULL в столбце ProductModelID обозначает все элементы столбца ProductModels. Значение NULL в столбцах Name обозначает все элементы столбца Products. Оператор CUBE возвращает следующие группы данных, полученных с помощью инструкции SELECT.
- Количество заказов для каждой модели продукта.
- Количество заказов для каждого продукта.
- Общее количество заказов.
Для каждого столбца, на который ссылается предложение GROUP BY, имеются перекрестные ссылки всех столбцов предложения GROUP BY. При этом статистическая функция SUM была применена заново. Поэтому в результирующем наборе появляются дополнительные строки. Данные, возвращаемые в результирующем наборе, имеют размерность n, определяемую количеством столбцов в предложении GROUP BY.
.gif "ms187731.note(ru-ru,SQL.90).gif") Примечание. |
|---|
| Убедитесь, что столбцы, указанные в предложении GROUP BY, действительно связаны между собой. Например, если на вход подаются столбцы Name и ProductID, оператор CUBE выдает неверные данные. Применение оператора CUBE к реально существующей иерархии, какой, например, являются годовые и квартальные продажи, дает неправильные результаты. Применение оператора ROLLUP является более эффективным. |
Ч. Совместное использование функции GROUPING с оператором CUBE
В следующем примере показано использование инструкции SELECT со статистической функцией SUM, предложением GROUP BY и оператором CUBE. Также используется функция GROUPING для обработки данных столбцов, перечисленных после предложения GROUP BY.
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).
Ш. Использование оператора 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)
Щ. Использование функции 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 (при значении столбца NULL), то функция GROUPING возвращает значение 1, что отражает набор всех значений. Функция GROUPING возвращает значение 0 в том случае, когда соответствующий столбец, независимо от его значения, не является результатом выполнения CUBE или ROLLUP. Возвращаемое значение имеет тип 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)
Э. Использование инструкции SELECT совместно с GROUP BY, ROLLUP и статистической функцией
В следующем примере используется запрос SELECT, содержащий статистическую функцию и предложение GROUP BY.
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 обозначает все элементы данного столбца.
При использовании инструкции SELECT без оператора ROLLUP создается только одна группировка. Запрос возвращает значение суммы для каждого уникального сочетания ProductModel, ProductModelID и ProductName:
ProductModel ProductModelID title SUM(qty)
Функция GROUPING может быть использована совместно с операторами CUBE или ROLLUP. Данная функция может быть применена к одному из столбцов списка выборки. В зависимости от того, был ли при группировке использован оператор ROLLUP, функция возвращает значение 1 или 0.
Ю. Использование подсказок оптимизатора 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
Я. Использование подсказок для операторов 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
а. Использование подсказок в запросе UNION
В следующем примере используется подсказка в запросе MERGE UNION.
USE AdventureWorks ;
GO
SELECT *
FROM HumanResources.Employee e1
UNION
SELECT *
FROM HumanResources.Employee e2
OPTION (MERGE UNION) ;
GO
б. Использование одиночного оператора UNION
При выполнении следующего примера в результирующий набор включается содержимое столбцов ProductModelID и Name таблиц ProductModel и Gloves.
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
в. Совместное использование UNION с инструкцией SELECT INTO
При выполнении следующего примера предложение INTO во второй инструкции SELECT указывает, что в таблице с именем 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 ;
г. Использование предложения UNION для двух инструкций SELECT совместно с ORDER BY
При использовании предложения UNION необходимо соблюдать порядок следования определенных параметров. В следующем примере приведены случаи правильного и неправильного использования предложения UNION с двумя инструкциями SELECT, при котором выходные столбцы должны быть переименованы.
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
д. Использование предложения UNION с тремя инструкциями SELECT: пояснение влияния скобок и ключевого слова ALL
В следующих примерах предложение UNION используется для комбинирования результатов из трех таблиц, содержащих по 5 одинаковых строк данных. В первом примере используется предложение UNION ALL, в результате чего выдаются все 15 строк. Во втором примере предложение UNION используется без ключевого слова ALL, что позволяет удалить повторяющиеся строки из комбинированного результата выполнения трех инструкций SELECT и вывести только 5 строк.
В третьем примере с первым предложением UNION используется ключевое слово ALL, а во втором предложении UNION вместо ключевого слова ALL используются скобки. Сначала выполняется второе предложение UNION, которое заключено в скобки. В результате возвращаются 5 строк, так как параметр ALL не используется и все повторяющиеся строки удаляются. Полученные 5 строк совмещаются с результатами выполнения первой инструкции SELECT с помощью ключевого слова UNION ALL. В данном случае повторяющиеся строки двух множеств не удаляются. Окончательный результат состоит из 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
Журнал изменений
| Версия | Журнал |
|---|---|
14 апреля 2006 г. |
|