WITH обобщенное_табличное_выражение (Transact-SQL)
Задается временно именованный результирующий набор, называемый обобщенным табличным выражением (ОТВ). Он извлекается при выполнении простого запроса и определяется в области выполнения одиночной инструкции SELECT, INSERT, UPDATE или DELETE. Это предложение может использоваться также в инструкции CREATE VIEW как часть определяющей ее инструкции SELECT. Обобщенное табличное выражение может включать ссылки на само себя. Такое выражение называется рекурсивным обобщенным табличным выражением.
Синтаксические обозначения в Transact-SQL
Синтаксис
[ WITH <common_table_expression> [ ,...n ] ]
<common_table_expression>::=
expression_name [ ( column_name [ ,...n ] ) ]
AS
( CTE_query_definition )
Аргументы
expression_name
Действительный идентификатор обобщенного табличного выражения. Имя аргумента expression_name должно отличаться от имени другого обобщенного табличного выражения, определенного в том же предложении WITH <общее_табличное_выражение>, но expression_name может совпадать с именем базовой таблицы или представления. Любая ссылка на аргумент expression_name в запросе использует обобщенное табличное выражение, но не базовый объект.column_name
Задается имя столбца в обобщенном табличном выражении. Повторяющиеся имена в определении одного CTE-выражения не допускаются. Количество заданных имен столбцов должно совпадать с количеством столбцов в результирующем наборе CTE_query_definition. Список имен столбцов необязателен только в том случае, если всем результирующим столбцам в определении запроса присвоены уникальные имена.CTE_query_definition
Задается инструкция SELECT, результирующий набор которой заполняет обобщенное табличное выражение. Инструкция SELECT для CTE_query_definition должна удовлетворять таким же требованиям, что и при создании представления, за исключением того, что CTE-выражение не может определять другое CTE-выражение. Дополнительные сведения см. в разделах «Примечания» и CREATE VIEW (Transact-SQL).Если определено несколько параметров CTE_query_definition, определения запроса должны быть соединены одним из следующих операторов работы с наборами: UNION ALL, UNION, EXCEPT или INTERSECT.
Замечания
Рекомендации по созданию и использованию обобщенных табличных выражений
Следующие рекомендации относятся к нерекурсивным обобщенным табличным выражениям. Рекомендации, применимые к рекурсивным обобщенным табличным выражениям, см. в расположенном ниже разделе «Рекомендации по определению и использованию рекурсивных обобщенных табличных выражений».
За CTE должны следовать одиночные инструкции SELECT, INSERT, UPDATE или DELETE, ссылающиеся на некоторые или на все столбцы CTE. CTE может задаваться также в инструкции CREATE VIEW как часть определяющей инструкции SELECT представления.
Несколько определений запросов CTE-выражений могут быть определены в нерекурсивных CTE-выражениях. Определения могут объединяться одним из следующих операторов работы с наборами: UNION ALL, UNION, INTERSECT или EXCEPT.
CTE-выражения могут иметь ссылки сами на себя, а также на CTE-выражения, определенные до этого в том же предложении WITH. Ссылки на определяемые далее CTE-выражения недопустимы.
Задание в одном CTE-выражении нескольких предложений WITH недопустимо. Например, если CTE_query_definition содержит вложенный запрос, этот вложенный запрос не может содержать вложенное предложение WITH, определяющее другое CTE-выражение.
Следующие предложения не могут использоваться в CTE_query_definition:
ORDER BY (за исключением случаев задания предложения TOP)
INTO
Предложение OPTION с указаниями запроса
FOR BROWSE
Если CTE-выражение используется в инструкции, являющейся частью пакета, за инструкцией, стоящей перед ней, должен следовать символ точки с запятой.
Запрос, ссылающийся на CTE-выражение, может использоваться для определения курсора.
В CTE-выражении могут быть ссылки на таблицы, находящиеся на удаленных серверах.
При выполнении CTE-выражения между указаниями, ссылающимися на CTE-выражение, может быть конфликт с другими указаниями, обнаруживаемыми, когда CTE-выражения обращаются к их базовым таблицам так же, как указания обращаются к представлениям в запросах. Когда это происходит, запрос возвращает ошибку.
Рекомендации по созданию и использованию рекурсивных обобщенных табличных выражений
Следующие рекомендации применимы к определению рекурсивных обобщенных табличных выражений.
Определение рекурсивного CTE-выражения должно содержать по крайней мере два определения CTE-выражений запросов, закрепленный элемент и рекурсивный элемент. Могут быть определены несколько закрепленных элементов и рекурсивных членов, однако все определения запросов закрепленного элемента должны быть поставлены перед первым определением рекурсивного члена. Все определения CTE-выражений запросовявляются закрепленными элементами, если только они не ссылаются на само CTE-выражение.
Закрепленные элементы должны объединяться одним из следующих операторов работы с наборами: UNION ALL, UNION, INTERSECT или EXCEPT. UNION ALL является единственным оператором установки, который может находиться между последним закрепленным элементом и первым рекурсивным членом, а также при объединении нескольких рекурсивных членов.
Количество столбцов членов указателя и рекурсивных членов должно совпадать.
Тип данных столбца в рекурсивном члене должен совпадать с типом данных соответствующего столбца в закрепленном элементе.
Предложение FROM рекурсивного члена должно ссылаться на CTE-выражение expression_name только один раз.
Следующие элементы недопустимы в CTE_query_definition рекурсивного члена:
SELECT DISTINCT
GROUP BY
PIVOT (Если уровень совместимости базы данных имеет значение 110. См. раздел Критические изменения в функциях компонента ядра СУБД в SQL Server 2012.)
HAVING
Скалярный агрегат
TOP
LEFT, RIGHT, OUTER JOIN (INNER JOIN допускается)
Вложенные запросы
Указание, применимое к рекурсивной ссылке на CTE-выражение в определении CTE_query_definition
Следующие рекомендации применимы к использованию рекурсивных обобщенных табличных выражений.
Все столбцы, возвращаемые рекурсивным CTE-выражением, могут содержать значения NULL, независимо от того, могут ли иметь значения NULL столбцы, возвращаемые участвующими инструкциями SELECT.
Неправильно составленное рекурсивное ОТВ может привести к бесконечному циклу. Например, если определение запроса рекурсивного члена возвращает одинаковые значения как для родительского, так и для дочернего столбца, то образуется бесконечный цикл. Для предотвращения бесконечного цикла можно ограничить количество уровней рекурсии, допустимых для определенной инструкции, при помощи указания MAXRECURSION и значения в диапазоне от 0 до 32767 в предложении OPTION инструкции INSERT, UPDATE, DELETE или SELECT. Это дает возможность контролировать выполнение инструкции до тех пор, пока не будет разрешена проблема с кодом, из-за которой происходит зацикливание программы. Серверное значение по умолчанию равно 100. Если указано значение 0, ограничения не применяются. В одной инструкции может быть указано только одно значение MAXRECURSION. Дополнительные сведения см. в разделе Указания запросов (Transact-SQL).
Представление, содержащее рекурсивное обобщенное табличное выражение, не может использоваться для обновления данных.
Курсоры могут определяться на запросах при помощи CTE-выражений. CTE-выражение является аргументом select_statement, который определяет результирующий набор курсора. Для рекурсивных CTE-выражений допустимы только однонаправленные и статические курсоры (курсоры моментального снимка). Если в рекурсивном CTE-выражении указан курсор другого типа, тип курсора преобразуется в статический.
В CTE-выражении могут быть ссылки на таблицы, находящиеся на удаленных серверах. Если на удаленный сервер имеются ссылки в рекурсивном члене CTE-выражения, создается буфер для каждой удаленной таблицы, так что к таблицам может многократно осуществляться локальный доступ. Если это запрос обобщенного табличного выражения, Index Spool/Lazy Spools отображается в плане запроса и будет иметь дополнительный предикат WITH STACK. Это один из способов подтверждения надлежащей рекурсии.
Аналитические и агрегатные функции в рекурсивной части обобщенных табличных выражений применяются для задания текущего уровня рекурсии, а не для задания обобщенных табличных выражений. Такие функции, как ROW_NUMBER, работают только с подмножествами данных, которые передаются им текущим уровнем рекурсии, но не со всем множеством данных, которые передаются в рекурсивную часть обобщенного табличного выражения. Дополнительные сведения см. в разделе К. Использование аналитических функций в рекурсивном табличном выражении.
Примеры
А.Создание простого обобщенного табличного выражения
В следующем примере выводится общее количество заказов на продажу в год для каждого коммерческого представителя в Компания Adventure Works Cycles.
USE AdventureWorks2012;
GO
-- Define the CTE expression name and column list.
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
-- Define the CTE query.
(
SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
)
-- Define the outer query referencing the CTE name.
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;
GO
Б.Использование обобщенного табличного выражения для ограничения общего и среднего количества отчетов
В следующем примере выводится среднее количество заказов на продажу за все годы для коммерческих представителей.
WITH Sales_CTE (SalesPersonID, NumberOfOrders)
AS
(
SELECT SalesPersonID, COUNT(*)
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
GROUP BY SalesPersonID
)
SELECT AVG(NumberOfOrders) AS "Average Sales Per Person"
FROM Sales_CTE;
GO
В.Использование нескольких определений ОТВ (обобщенных табличных выражений) в одном запросе
В следующем примере показано, как определить несколько ОТВ в одном запросе. Обратите внимание, что для разделения двух отдельных определений ОТВ используется запятая.
USE AdventureWorks2012;
GO
WITH Sales_CTE (SalesPersonID, TotalSales, SalesYear)
AS
-- Define the first CTE query.
(
SELECT SalesPersonID, SUM(TotalDue) AS TotalSales, YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
GROUP BY SalesPersonID, YEAR(OrderDate)
)
, -- Use a comma to separate multiple CTE definitions.
-- Define the second CTE query, which returns sales quota data by year for each sales person.
Sales_Quota_CTE (BusinessEntityID, SalesQuota, SalesQuotaYear)
AS
(
SELECT BusinessEntityID, SUM(SalesQuota)AS SalesQuota, YEAR(QuotaDate) AS SalesQuotaYear
FROM Sales.SalesPersonQuotaHistory
GROUP BY BusinessEntityID, YEAR(QuotaDate)
)
-- Define the outer query by referencing columns from both CTEs.
SELECT SalesPersonID
, SalesYear
, FORMAT(TotalSales,'C','ru-ru') AS TotalSales
, SalesQuotaYear
, FORMAT (SalesQuota,'C','ru-ru') AS SalesQuota
, FORMAT (TotalSales -SalesQuota, 'C','ru-ru') AS Amt_Above_or_Below_Quota
FROM Sales_CTE
JOIN Sales_Quota_CTE ON Sales_Quota_CTE.BusinessEntityID = Sales_CTE.SalesPersonID
AND Sales_CTE.SalesYear = Sales_Quota_CTE.SalesQuotaYear
ORDER BY SalesPersonID, SalesYear;
GO
Здесь приводится частичный результирующий набор.
SalesPersonID SalesYear TotalSales SalesQuotaYear SalesQuota Amt_Above_or_Below_Quota
------------- --------- ----------- -------------- ---------- ----------------------------------
274 2005 $32,567.92 2005 $35,000.00 ($2,432.08)
274 2006 $406,620.07 2006 $455,000.00 ($48,379.93)
274 2007 $515,622.91 2007 $544,000.00 ($28,377.09)
274 2008 $281,123.55 2008 $271,000.00 $10,123.55
Г.Использование рекурсивного обобщенного табличного выражения для отображения нескольких уровней рекурсии
В следующем примере представлен иерархический список руководителей и служащих, отчитывающихся перед ними. Пример начинается с создания и заполнения таблицы dbo.MyEmployees.
-- Create an Employee table.
CREATE TABLE dbo.MyEmployees
(
EmployeeID smallint NOT NULL,
FirstName nvarchar(30) NOT NULL,
LastName nvarchar(40) NOT NULL,
Title nvarchar(50) NOT NULL,
DeptID smallint NOT NULL,
ManagerID int NULL,
CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC)
);
-- Populate the table with values.
INSERT INTO dbo.MyEmployees VALUES
(1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL)
,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1)
,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273)
,(275, N'Michael', N'Blythe', N'Sales Representative',3,274)
,(276, N'Linda', N'Mitchell', N'Sales Representative',3,274)
,(285, N'Syed', N'Abbas', N'Pacific Sales Manager',3,273)
,(286, N'Lynn', N'Tsoflias', N'Sales Representative',3,285)
,(16, N'David',N'Bradley', N'Marketing Manager', 4, 273)
,(23, N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);
USE AdventureWorks2012;
GO
WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS
(
SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel
FROM dbo.MyEmployees
WHERE ManagerID IS NULL
UNION ALL
SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1
FROM dbo.MyEmployees AS e
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, Title, EmployeeLevel
FROM DirectReports
ORDER BY ManagerID;
GO
Д.Использование рекурсивного обобщенного табличного выражения для отображения двух уровней рекурсии
В следующем примере представлены руководители и отчитывающиеся перед ними служащие. Количество возвращаемых уровней ограничено двумя.
USE AdventureWorks2012;
GO
WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS
(
SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel
FROM dbo.MyEmployees
WHERE ManagerID IS NULL
UNION ALL
SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1
FROM dbo.MyEmployees AS e
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, Title, EmployeeLevel
FROM DirectReports
WHERE EmployeeLevel <= 2 ;
GO
Е.Использование рекурсивного обобщенного табличного выражения для отображения иерархического списка
Следующий пример строится на примере D с добавлением имен руководителей и служащих и соответствующих им должностей. Иерархия руководителей и служащих дополнительно выделяется при выполнении соответствующих отступов на каждом уровне.
USE AdventureWorks2012;
GO
WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)
AS (SELECT CONVERT(varchar(255), e.FirstName + ' ' + e.LastName),
e.Title,
e.EmployeeID,
1,
CONVERT(varchar(255), e.FirstName + ' ' + e.LastName)
FROM dbo.MyEmployees AS e
WHERE e.ManagerID IS NULL
UNION ALL
SELECT CONVERT(varchar(255), REPLICATE ('| ' , EmployeeLevel) +
e.FirstName + ' ' + e.LastName),
e.Title,
e.EmployeeID,
EmployeeLevel + 1,
CONVERT (varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' +
LastName)
FROM dbo.MyEmployees AS e
JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM DirectReports
ORDER BY Sort;
GO
Ж.Использование подсказки MAXRECURSION для отмены инструкции
Подсказка MAXRECURSION может использоваться для предотвращения входа в бесконечный цикл из-за неверно сформированного рекурсивного CTE-выражения. В следующем примере специально создается бесконечный цикл и используется указание MAXRECURSION для ограничения количества уровней рекурсии до двух.
USE AdventureWorks2012;
GO
--Creates an infinite loop
WITH cte (EmployeeID, ManagerID, Title) as
(
SELECT EmployeeID, ManagerID, Title
FROM dbo.MyEmployees
WHERE ManagerID IS NOT NULL
UNION ALL
SELECT cte.EmployeeID, cte.ManagerID, cte.Title
FROM cte
JOIN dbo.MyEmployees AS e
ON cte.ManagerID = e.EmployeeID
)
--Uses MAXRECURSION to limit the recursive levels to 2
SELECT EmployeeID, ManagerID, Title
FROM cte
OPTION (MAXRECURSION 2);
GO
После исправления ошибки в коде подсказка MAXRECURSION больше не нужна. В следующем примере приводится правильный код.
USE AdventureWorks2012;
GO
WITH cte (EmployeeID, ManagerID, Title)
AS
(
SELECT EmployeeID, ManagerID, Title
FROM dbo.MyEmployees
WHERE ManagerID IS NOT NULL
UNION ALL
SELECT e.EmployeeID, e.ManagerID, e.Title
FROM dbo.MyEmployees AS e
JOIN cte ON e.ManagerID = cte.EmployeeID
)
SELECT EmployeeID, ManagerID, Title
FROM cte;
GO
З.Использование обобщенного табличного выражения для выборочного прохождения рекурсивной связи в инструкции SELECT
В следующем примере показана иерархия узлов и компонентов продукции, необходимых для создания велосипеда для ProductAssemblyID = 800.
USE AdventureWorks2012;
GO
WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS
(
SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,
b.EndDate, 0 AS ComponentLevel
FROM Production.BillOfMaterials AS b
WHERE b.ProductAssemblyID = 800
AND b.EndDate IS NULL
UNION ALL
SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,
bom.EndDate, ComponentLevel + 1
FROM Production.BillOfMaterials AS bom
INNER JOIN Parts AS p
ON bom.ProductAssemblyID = p.ComponentID
AND bom.EndDate IS NULL
)
SELECT AssemblyID, ComponentID, Name, PerAssemblyQty, EndDate,
ComponentLevel
FROM Parts AS p
INNER JOIN Production.Product AS pr
ON p.ComponentID = pr.ProductID
ORDER BY ComponentLevel, AssemblyID, ComponentID;
GO
И.Использование рекурсивного CTE-выражения в инструкции UPDATE
В следующем примере обновляется значение PerAssemblyQty для всех деталей, используемых при сборке продукта «Road-550-W Yellow, 44» (ProductAssemblyID 800). Обобщенное табличное выражение возвращает иерархический список деталей, которые непосредственно используются для сборки ProductAssemblyID 800, и компонентов, которые используются для сборки этих деталей и т. д. Изменяются только строки, возвращенные обобщенным табличным выражением.
USE AdventureWorks2012;
GO
WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS
(
SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,
b.EndDate, 0 AS ComponentLevel
FROM Production.BillOfMaterials AS b
WHERE b.ProductAssemblyID = 800
AND b.EndDate IS NULL
UNION ALL
SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,
bom.EndDate, ComponentLevel + 1
FROM Production.BillOfMaterials AS bom
INNER JOIN Parts AS p
ON bom.ProductAssemblyID = p.ComponentID
AND bom.EndDate IS NULL
)
UPDATE Production.BillOfMaterials
SET PerAssemblyQty = c.PerAssemblyQty * 2
FROM Production.BillOfMaterials AS c
JOIN Parts AS d ON c.ProductAssemblyID = d.AssemblyID
WHERE d.ComponentLevel = 0;
К.Использование нескольких привязок и рекурсивных элементов
В следующем примере несколько членов указателя и рекурсивных членов используются для возврата всех предков указанного лица. Создается и заполняется значениями таблица для формирования генеалогии семьи, возвращаемой рекурсивным обобщенным табличным выражением.
-- Genealogy table
IF OBJECT_ID('dbo.Person','U') IS NOT NULL DROP TABLE dbo.Person;
GO
CREATE TABLE dbo.Person(ID int, Name varchar(30), Mother int, Father int);
GO
INSERT dbo.Person
VALUES(1, 'Sue', NULL, NULL)
,(2, 'Ed', NULL, NULL)
,(3, 'Emma', 1, 2)
,(4, 'Jack', 1, 2)
,(5, 'Jane', NULL, NULL)
,(6, 'Bonnie', 5, 4)
,(7, 'Bill', 5, 4);
GO
-- Create the recursive CTE to find all of Bonnie's ancestors.
WITH Generation (ID) AS
(
-- First anchor member returns Bonnie's mother.
SELECT Mother
FROM dbo.Person
WHERE Name = 'Bonnie'
UNION
-- Second anchor member returns Bonnie's father.
SELECT Father
FROM dbo.Person
WHERE Name = 'Bonnie'
UNION ALL
-- First recursive member returns male ancestors of the previous generation.
SELECT Person.Father
FROM Generation, Person
WHERE Generation.ID=Person.ID
UNION ALL
-- Second recursive member returns female ancestors of the previous generation.
SELECT Person.Mother
FROM Generation, dbo.Person
WHERE Generation.ID=Person.ID
)
SELECT Person.ID, Person.Name, Person.Mother, Person.Father
FROM Generation, dbo.Person
WHERE Generation.ID = Person.ID;
GO
Л.Использование аналитических функций в рекурсивном табличном выражении
Следующий пример демонстрирует проблему, которая может возникнуть при использовании аналитической или агрегатной функции в рекурсивной части ОТВ.
DECLARE @t1 TABLE (itmID int, itmIDComp int);
INSERT @t1 VALUES (1,10), (2,10);
DECLARE @t2 TABLE (itmID int, itmIDComp int);
INSERT @t2 VALUES (3,10), (4,10);
WITH vw AS
(
SELECT itmIDComp, itmID
FROM @t1
UNION ALL
SELECT itmIDComp, itmID
FROM @t2
)
,r AS
(
SELECT t.itmID AS itmIDComp
, NULL AS itmID
,CAST(0 AS bigint) AS N
,1 AS Lvl
FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) AS t (itmID)
UNION ALL
SELECT t.itmIDComp
, t.itmID
, ROW_NUMBER() OVER(PARTITION BY t.itmIDComp ORDER BY t.itmIDComp, t.itmID) AS N
, Lvl + 1
FROM r
JOIN vw AS t ON t.itmID = r.itmIDComp
)
SELECT Lvl, N FROM r;
Следующие результаты являются ожидаемыми результатами выполнения запроса.
Lvl N
1 0
1 0
1 0
1 0
2 4
2 3
2 2
2 1
Следующие результаты являются фактическими результатами выполнения запроса.
Lvl N
1 0
1 0
1 0
1 0
2 1
2 1
2 1
2 1
N возвращает 1 для каждого прохода рекурсивной части ОТВ, так как в ROWNUMBER передается только подмножество данных для данного уровня рекурсии. Для каждой итерации рекурсивной части запроса в ROWNUMBER передается только одна строка.
См. также
Справочник
EXCEPT и INTERSECT (Transact-SQL)