Поделиться через


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, INTERSECT или EXCEPT. Дополнительные сведения об использовании определений рекурсивных CTE-выражений запросов см. в следующем разделе «Примечания» и в разделе Рекурсивные запросы, использующие обобщенные табличные выражения.

Замечания

Рекомендации по созданию и использованию CTE-выражений

Следующие инструкции применимы к нерекурсивным CTE-выражениям. Инструкции, применимые к рекурсивным CTE-выражениям, содержатся в приводимом далее разделе «Рекомендации по определению и использованию рекурсивных CTE-выражений».

  • За 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:
    • COMPUTE или COMPUTE BY
    • ORDER BY (за исключением случаев задания предложения TOP)
    • INTO
    • Предложение OPTION с подсказками в запросе
    • FOR XML
    • FOR BROWSE
  • Если CTE-выражение используется в инструкции, являющейся частью пакета, за инструкцией, стоящей перед ней, должен следовать символ точки с запятой.
  • Запрос, ссылающийся на CTE-выражение, может использоваться для определения курсора.
  • В 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;
    • HAVING;
    • скалярный агрегат;
    • TOP;
    • LEFT, RIGHT, OUTER JOIN (INNER JOIN допускается);
    • вложенные запросы;
    • подсказка, применимая к рекурсивной ссылке на CTE-выражение в определении CTE_query_definition.

Следующие инструкции применимы к использованию рекурсивных CTE-выражений.

  • Все столбцы, возвращаемые рекурсивным CTE-выражением, могут содержать значения NULL, независимо от того, могут ли иметь значения NULL столбцы, возвращаемые участвующими инструкциями SELECT.
  • Неправильно составленное рекурсивное CTE-выражение может привести к бесконечному циклу. Например, если определение запроса рекурсивного члена возвращает одинаковые значения как для родительского, так и для дочернего столбца, то образуется бесконечный цикл. Для предотвращения бесконечного цикла можно ограничить количество уровней рекурсии, допустимых для определенной инструкции, при помощи подсказки MAXRECURSION и значения в диапазоне от 0 до 32767 в предложении OPTION инструкции INSERT, UPDATE, DELETE или SELECT. Это дает возможность контролировать выполнение инструкции до тех пор, пока не будет разрешена проблема с кодом, из-за которой происходит зацикливание программы. Значение по умолчанию уровня сервера равно 100. При указании 0 не применяется никакого ограничения. В одной инструкции может быть указано только одно значение MAXRECURSION. Дополнительные сведения см. в разделе Подсказка в запросе (Transact-SQL).
  • Представление, содержащее рекурсивное общее табличное выражение, не может использоваться для обновления данных.
  • Курсоры могут определяться на запросах при помощи CTE-выражений. CTE-выражение является аргументом select_statement, который определяет результирующий набор курсора. Для рекурсивных CTE-выражений допустимы только однонаправленные и статические курсоры (курсоры моментального снимка). Если в рекурсивном CTE-выражении указан курсор другого типа, тип курсора преобразуется в статический.
  • В CTE-выражении могут быть ссылки на таблицы, находящиеся на удаленных серверах. Если на удаленный сервер имеются ссылки в рекурсивном члене CTE-выражения, создается буфер для каждой удаленной таблицы, так что к таблицам может многократно осуществляться локальный доступ.

Примеры

А. Создание простого общего табличного выражения

В следующем примере несколько служащих предоставляют отчеты непосредственно каждому руководителю в Adventure Works Cycles.

USE AdventureWorks;
GO
WITH DirReps(ManagerID, DirectReports) AS 
(
    SELECT ManagerID, COUNT(*) 
    FROM HumanResources.Employee AS e
    WHERE ManagerID IS NOT NULL
    GROUP BY ManagerID
)
SELECT ManagerID, DirectReports 
FROM DirReps 
ORDER BY ManagerID;
GO

Б. Использование общего табличного выражения для ограничения общего и среднего количества отчетов

В следующем примере показано среднее количество служащих, отсылающих доклады руководителям.

WITH DirReps (Manager, DirectReports) AS 
(
    SELECT ManagerID, COUNT(*) AS DirectReports
    FROM HumanResources.Employee
    GROUP BY ManagerID
) 
SELECT AVG(DirectReports) AS [Average Number of Direct Reports]
FROM DirReps 
WHERE DirectReports>= 2 ;
GO

В. Неоднократное обращение к общему табличному выражению

В следующем примере представлено общее количество заказов на продажу и дата последнего заказа на продажу в таблице SalesOrderHeader для каждого продавца. В выполняющейся инструкции ссылка на CTE-выражение выполняется два раза: один раз для возврата выбранных столбцов для продавцов и повторно для получения подобной подробной информации для руководителей продавцов. Данные как для продавца, так и для руководителя возвращаются в одной строке.

USE AdventureWorks;
GO
WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)
AS
(
    SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
    FROM Sales.SalesOrderHeader
    GROUP BY SalesPersonID
)
SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
    E.ManagerID, OM.NumberOfOrders, OM.MaxDate
FROM HumanResources.Employee AS E
    JOIN Sales_CTE AS OS
    ON E.EmployeeID = OS.SalesPersonID
    LEFT OUTER JOIN Sales_CTE AS OM
    ON E.ManagerID = OM.SalesPersonID
ORDER BY E.EmployeeID;
GO

Г. Использование рекурсивного общего табличного выражения для отображения нескольких уровней рекурсии

В следующем примере представлен иерархический список руководителей и служащих, отчитывающихся перед ними.

USE AdventureWorks;
GO
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
    FROM HumanResources.Employee e
        INNER JOIN DirectReports d
        ON e.ManagerID = d.EmployeeID 
)
SELECT ManagerID, EmployeeID, EmployeeLevel 
FROM DirectReports ;
GO

Д. Использование рекурсивного общего табличного выражения для отображения двух уровней рекурсии

В следующем примере представлены руководители и отчитывающиеся перед ними служащие. Количество возвращаемых уровней ограничено двумя.

USE AdventureWorks;
GO
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
    FROM HumanResources.Employee e
        INNER JOIN DirectReports d
        ON e.ManagerID = d.EmployeeID 
)
SELECT ManagerID, EmployeeID, EmployeeLevel 
FROM DirectReports 
WHERE EmployeeLevel <= 2 ;
GO

Е. Использование рекурсивного общего табличного выражения для отображения иерархического списка

Следующий пример строится на примере с добавлением имен руководителей и служащих и соответствующих им должностей. Иерархия руководителей и служащих дополнительно выделяется при выполнении соответствующих отступов на каждом уровне.

USE AdventureWorks;
GO
WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)
AS (SELECT CONVERT(varchar(255), c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        1,
        CONVERT(varchar(255), c.FirstName + ' ' + c.LastName)
    FROM HumanResources.Employee AS e
    JOIN Person.Contact AS c ON e.ContactID = c.ContactID 
    WHERE e.ManagerID IS NULL
    UNION ALL
    SELECT CONVERT(varchar(255), REPLICATE ('| ' , EmployeeLevel) +
        c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        EmployeeLevel + 1,
        CONVERT (varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' + 
                 LastName)
    FROM HumanResources.Employee as e
    JOIN Person.Contact AS c ON e.ContactID = c.ContactID
    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 AdventureWorks;
GO
--Creates an infinite loop
WITH cte (EmployeeID, ManagerID, Title) as
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT cte.EmployeeID, cte.ManagerID, cte.Title
    FROM cte 
    JOIN  HumanResources.Employee 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 AdventureWorks;
GO
WITH cte (EmployeeID, ManagerID, Title)
AS
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT  e.EmployeeID, e.ManagerID, e.Title
    FROM HumanResources.Employee AS e
    JOIN cte ON e.ManagerID = cte.EmployeeID
)
SELECT EmployeeID, ManagerID, Title
FROM cte;
GO

З. Использование общего табличного выражения для выборочного прохождения рекурсивной связи в инструкции SELECT

В следующем примере показана иерархия узлов и компонентов продукции, необходимых для создания велосипеда для ProductAssemblyID = 800.

USE AdventureWorks;
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

В следующем примере значение VacationHours изменяется на 25 процентов для всех служащих, отчитывающихся непосредственно или не непосредственно перед ManagerID 12. Общее табличное выражение возвращает иерархический список служащих, отчитывающихся непосредственно перед ManagerID 12, служащих, отчитывающихся перед этими служащими, и т.д. Изменяются только строки, возвращаемые общим табличным выражением.

USE AdventureWorks;
GO
WITH DirectReports(EmployeeID, NewVacationHours, EmployeeLevel)
AS
(SELECT e.EmployeeID, e.VacationHours, 1
  FROM HumanResources.Employee AS e
  WHERE e.ManagerID = 12
  UNION ALL
  SELECT e.EmployeeID, e.VacationHours, EmployeeLevel + 1
  FROM HumanResources.Employee as e
  JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
UPDATE HumanResources.Employee
SET VacationHours = VacationHours * 1.25
FROM HumanResources.Employee AS e
JOIN DirectReports AS d ON e.EmployeeID = d.EmployeeID;
GO

К. Использование нескольких членов указателя и рекурсивных членов

В следующем примере несколько членов указателя и рекурсивных членов используются для возврата всех предков указанного лица. Создается и заполняется значениями таблица для формирования генеалогии семьи, возвращаемой рекурсивным CTE-выражением.

-- Genealogy table
IF OBJECT_ID('Person','U') IS NOT NULL DROP TABLE Person;
GO
CREATE TABLE Person(ID int, Name varchar(30), Mother int, Father int);
GO
INSERT Person VALUES(1, 'Sue', NULL, NULL);
INSERT Person VALUES(2, 'Ed', NULL, NULL);
INSERT Person VALUES(3, 'Emma', 1, 2);
INSERT Person VALUES(4, 'Jack', 1, 2);
INSERT Person VALUES(5, 'Jane', NULL, NULL);
INSERT Person VALUES(6, 'Bonnie', 5, 4);
INSERT Person VALUES(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 Person
    WHERE Name = 'Bonnie'
UNION
-- Second anchor member returns Bonnie's father.
    SELECT Father 
    FROM 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, Person
    WHERE Generation.ID=Person.ID
)
SELECT Person.ID, Person.Name, Person.Mother, Person.Father
FROM Generation, Person
WHERE Generation.ID = Person.ID;
GO

См. также

Справочник

CREATE VIEW (Transact-SQL)
DELETE (Transact-SQL)
EXCEPT и INTERSECT (Transact-SQL)
INSERT (Transact-SQL)
SELECT (Transact-SQL)
UPDATE (Transact-SQL)

Другие ресурсы

Рекурсивные запросы, использующие обобщенные табличные выражения
Применение обобщенных табличных выражений

Справка и поддержка

Получение помощи по SQL Server 2005