WITH обобщенное_табличное_выражение (Transact-SQL)WITH common_table_expression (Transact-SQL)

ОБЛАСТЬ ПРИМЕНЕНИЯ: даSQL Server даБаза данных SQL Azure даAzure Synapse Analytics (хранилище данных SQL) даParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

Задается временно именованный результирующий набор, называемый обобщенным табличным выражением (ОТВ).Specifies a temporary named result set, known as a common table expression (CTE). Он получается при выполнении простого запроса и определяется в области выполнения одиночной инструкции SELECT, INSERT, UPDATE, DELETE или MERGE.This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE or MERGE statement. Это предложение может использоваться также в инструкции CREATE VIEW как часть определяющей ее инструкции SELECT.This clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement. Обобщенное табличное выражение может включать ссылки на само себя.A common table expression can include references to itself. Такое выражение называется рекурсивным обобщенным табличным выражением.This is referred to as a recursive common table expression.

Значок ссылки на раздел Синтаксические обозначения в Transact-SQLTopic link icon Transact-SQL Syntax Conventions

СинтаксисSyntax

[ WITH <common_table_expression> [ ,...n ] ]  
  
<common_table_expression>::=  
    expression_name [ ( column_name [ ,...n ] ) ]  
    AS  
    ( CTE_query_definition )  

АргументыArguments

expression_nameexpression_name
Является допустимым идентификатором для обобщенного табличного выражения.Is a valid identifier for the common table expression. Имя выражения expression_name должно отличаться от имени другого обобщенного табличного выражения, определенного в том же предложении WITH <общее_табличное_выражение>, но expression_name может совпадать с именем базовой таблицы или представления.expression_name must be different from the name of any other common table expression defined in the same WITH <common_table_expression> clause, but expression_name can be the same as the name of a base table or view. Любая ссылка на аргумент expression_name в запросе использует обобщенное табличное выражение, но не базовый объект.Any reference to expression_name in the query uses the common table expression and not the base object.

column_namecolumn_name
Задается имя столбца в обобщенном табличном выражении.Specifies a column name in the common table expression. Повторяющиеся имена в определении одного обобщенного табличного выражения не допускаются.Duplicate names within a single CTE definition are not allowed. Количество заданных имен столбцов должно совпадать с количеством столбцов в результирующем наборе CTE_query_definition.The number of column names specified must match the number of columns in the result set of the CTE_query_definition. Список имен столбцов необязателен только в том случае, если всем результирующим столбцам в определении запроса присвоены уникальные имена.The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.

CTE_query_definitionCTE_query_definition
Задается инструкция SELECT, результирующий набор которой заполняет обобщенное табличное выражение.Specifies a SELECT statement whose result set populates the common table expression. Инструкция SELECT для CTE_query_definition должна соответствовать таким же требованиям, что и при создании представления, за исключением того, что обобщенное табличное выражение (ОТВ) не может определять другое ОТВ.The SELECT statement for CTE_query_definition must meet the same requirements as for creating a view, except a CTE cannot define another CTE. Дополнительные сведения см. в подразделе "Замечания" и разделе CREATE VIEW (Transact-SQL).For more information, see the Remarks section and CREATE VIEW (Transact-SQL).

Если определено несколько параметров CTE_query_definition, определения запроса должны быть соединены одним из следующих операторов над множествами: UNION ALL, UNION, EXCEPT или INTERSECT.If more than one CTE_query_definition is defined, the query definitions must be joined by one of these set operators: UNION ALL, UNION, EXCEPT, or INTERSECT.

RemarksRemarks

Рекомендации по созданию и использованию обобщенных табличных выраженийGuidelines for Creating and Using Common Table Expressions

Следующие рекомендации относятся к нерекурсивным обобщенным табличным выражениям.The following guidelines apply to nonrecursive common table expressions. Рекомендации, применимые к рекурсивным обобщенным табличным выражениям, см. в расположенном ниже разделе Рекомендации по определению и использованию рекурсивных обобщенных табличных выражений.For guidelines that apply to recursive common table expressions, see Guidelines for Defining and Using Recursive Common Table Expressions that follows.

  • За обобщенным табличным выражением (ОТВ) должны следовать одиночные инструкцииSELECT, INSERT, UPDATE или DELETE, ссылающиеся на некоторые или на все столбцы ОТВ.A CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE statement that references some or all the CTE columns. Обобщенное табличное выражение может задаваться также в инструкции CREATE VIEW как часть определяющей инструкции SELECT представления.A CTE can also be specified in a CREATE VIEW statement as part of the defining SELECT statement of the view.

  • Несколько определений запросов обобщенных табличных выражений (ОТВ) могут быть определены в нерекурсивных ОТВ.Multiple CTE query definitions can be defined in a nonrecursive CTE. Определения могут объединяться одним из следующих операторов над множествами: UNION ALL, UNION, INTERSECT или EXCEPT.The definitions must be combined by one of these set operators: UNION ALL, UNION, INTERSECT, or EXCEPT.

  • Обобщенные табличные выражения (ОТВ) могут иметь ссылки на самих себя, а также на ОТВ, определенные до этого в том же предложении WITH.A CTE can reference itself and previously defined CTEs in the same WITH clause. Ссылки на определяемые далее обобщенные табличные выражения недопустимы.Forward referencing is not allowed.

  • Задание в одном обобщенном табличном выражении нескольких предложений WITH недопустимо.Specifying more than one WITH clause in a CTE is not allowed. Например, если CTE_query_definition содержит вложенный запрос, этот вложенный запрос не может содержать вложенное предложение WITH, определяющее другое обобщенное табличное выражение.For example, if a CTE_query_definition contains a subquery, that subquery cannot contain a nested WITH clause that defines another CTE.

  • Следующие предложения не могут использоваться в CTE_query_definition:The following clauses cannot be used in the CTE_query_definition:

    • ORDER BY (за исключением случаев задания предложения TOP)ORDER BY (except when a TOP clause is specified)

    • INTO

    • Предложение OPTION с указаниями запросаOPTION clause with query hints

    • FOR BROWSE

  • Если обобщенное табличное выражение используется в инструкции, являющейся частью пакета, то за инструкцией, стоящей перед ней, должен следовать символ точки с запятой.When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.

  • Запрос, ссылающийся на обобщенное табличное выражение, может использоваться для определения курсора.A query referencing a CTE can be used to define a cursor.

  • В обобщенном табличном выражении могут быть ссылки на таблицы, находящиеся на удаленных серверах.Tables on remote servers can be referenced in the CTE.

  • При выполнении обобщенного табличного выражения (ОТВ) между указаниями, ссылающимися на ОТВ, может быть конфликт с другими указаниями, обнаруживаемыми, когда ОТВ обращаются к их базовым таблицам, так же, как если бы указания ссылались на представления в запросах.When executing a CTE, any hints that reference a CTE may conflict with other hints that are discovered when the CTE accesses its underlying tables, in the same manner as hints that reference views in queries. Когда это происходит, запрос возвращает ошибку.When this occurs, the query returns an error.

Рекомендации по созданию и использованию рекурсивных обобщенных табличных выраженийGuidelines for Defining and Using Recursive Common Table Expressions

Следующие рекомендации применимы к определению рекурсивных обобщенных табличных выражений.The following guidelines apply to defining a recursive common table expression:

  • Определение рекурсивного обобщенного табличного выражения должно содержать по крайней мере два определения обобщенного табличного выражения запросов — закрепленный элемент и рекурсивный элемент.The recursive CTE definition must contain at least two CTE query definitions, an anchor member and a recursive member. Может быть определено несколько закрепленных элементов и рекурсивных элементов, однако все определения запросов закрепленного элемента должны быть поставлены перед первым определением рекурсивного элемента.Multiple anchor members and recursive members can be defined; however, all anchor member query definitions must be put before the first recursive member definition. Все определения обобщенных табличных выражений запросов (ОТВ) являются закрепленными элементами, если только они не ссылаются на само ОТВ.All CTE query definitions are anchor members unless they reference the CTE itself.

  • Закрепленные элементы должны объединяться одним из следующих операторов над множествами: UNION ALL, UNION, INTERSECT или EXCEPT.Anchor members must be combined by one of these set operators: UNION ALL, UNION, INTERSECT, or EXCEPT. UNION ALL является единственным оператором над множествами, который может находиться между последним закрепленным элементом и первым рекурсивным элементом, а также может применяться при объединении нескольких рекурсивных элементов.UNION ALL is the only set operator allowed between the last anchor member and first recursive member, and when combining multiple recursive members.

  • Количество столбцов членов указателя и рекурсивных элементов должно совпадать.The number of columns in the anchor and recursive members must be the same.

  • Тип данных столбца в рекурсивном элементе должен совпадать с типом данных соответствующего столбца в закрепленном элементе.The data type of a column in the recursive member must be the same as the data type of the corresponding column in the anchor member.

  • Предложение FROM рекурсивного элемента должно ссылаться на обобщенное табличное выражение expression_name только один раз.The FROM clause of a recursive member must refer only one time to the CTE expression_name.

  • Следующие элементы недопустимы в определении CTE_query_definition рекурсивного элемента:The following items are not allowed in the CTE_query_definition of a recursive member:

    • SELECT DISTINCT

    • GROUP BY

    • PIVOT (Если уровень совместимости базы данных имеет значение 110 или больше.PIVOT (When the database compatibility level is 110 or higher. См. раздел Критические изменения в функциях компонента ядра СУБД в SQL Server 2016).See Breaking Changes to Database Engine Features in SQL Server 2016.)

    • HAVING

    • Скалярное агрегированиеScalar aggregation

    • TOP

    • LEFT, RIGHT, OUTER JOIN (INNER JOIN допускается)LEFT, RIGHT, OUTER JOIN (INNER JOIN is allowed)

    • Вложенные запросыSubqueries

    • Указание, применимое к рекурсивной ссылке на обобщенное табличное выражение в определении CTE_query_definition.A hint applied to a recursive reference to a CTE inside a CTE_query_definition.

Следующие рекомендации применимы к использованию рекурсивных обобщенных табличных выражений.The following guidelines apply to using a recursive common table expression:

  • Все столбцы, возвращаемые рекурсивным обобщенным табличным выражением, могут содержать значения NULL, независимо от того, могут ли иметь значения NULL столбцы, возвращаемые участвующими инструкциями SELECT.All columns returned by the recursive CTE are nullable regardless of the nullability of the columns returned by the participating SELECT statements.

  • Неправильно составленное рекурсивное ОТВ может привести к бесконечному циклу.An incorrectly composed recursive CTE may cause an infinite loop. Например, если определение запроса рекурсивного элемента возвращает одинаковые значения как для родительского, так и для дочернего столбца, то образуется бесконечный цикл.For example, if the recursive member query definition returns the same values for both the parent and child columns, an infinite loop is created. Для предотвращения бесконечного цикла можно ограничить количество уровней рекурсии, допустимых для определенной инструкции, при помощи указания MAXRECURSION и значения в диапазоне от 0 до 32 767 в предложении OPTION инструкции INSERT, UPDATE, DELETE или SELECT.To prevent an infinite loop, you can limit the number of recursion levels allowed for a particular statement by using the MAXRECURSION hint and a value between 0 and 32,767 in the OPTION clause of the INSERT, UPDATE, DELETE, or SELECT statement. Это дает возможность контролировать выполнение инструкции до тех пор, пока не будет разрешена проблема с кодом, из-за которой происходит зацикливание программы.This lets you control the execution of the statement until you resolve the code problem that is creating the loop. Серверное значение по умолчанию равно 100.The server-wide default is 100. Если указано значение 0, ограничения не применяются.When 0 is specified, no limit is applied. В одной инструкции может быть указан только одно значение MAXRECURSION.Only one MAXRECURSION value can be specified per statement. Дополнительные сведения см. в разделе Указания запросов (Transact-SQL).For more information, see Query Hints (Transact-SQL).

  • Представление, содержащее рекурсивное обобщенное табличное выражение, не может использоваться для обновления данных.A view that contains a recursive common table expression cannot be used to update data.

  • Курсоры могут определяться на запросах при помощи обобщенных табличных выражений.Cursors may be defined on queries using CTEs. Обобщенное табличное выражение является аргументом select_statement, который определяет результирующий набор курсора.The CTE is the select_statement argument that defines the result set of the cursor. Для рекурсивных обобщенных табличных выражений допустимы только однонаправленные и статические курсоры (курсоры моментального снимка).Only fast forward-only and static (snapshot) cursors are allowed for recursive CTEs. Если в рекурсивном обобщенном табличном выражении указан курсор другого типа, тип курсора преобразуется в статический.If another cursor type is specified in a recursive CTE, the cursor type is converted to static.

  • В обобщенном табличном выражении могут быть ссылки на таблицы, находящиеся на удаленных серверах.Tables on remote servers may be referenced in the CTE. Если на удаленный сервер имеются ссылки в рекурсивном элементе обобщенного табличного выражения, создается буфер для каждой удаленной таблицы, так что к таблицам может многократно осуществляться локальный доступ.If the remote server is referenced in the recursive member of the CTE, a spool is created for each remote table so the tables can be repeatedly accessed locally. Если это запрос обобщенного табличного выражения, Index Spool/Lazy Spools отображается в плане запроса и будет иметь дополнительный предикат WITH STACK.If it is a CTE query, Index Spool/Lazy Spools is displayed in the query plan and will have the additional WITH STACK predicate. Это один из способов подтверждения надлежащей рекурсии.This is one way to confirm proper recursion.

  • Аналитические и агрегатные функции в рекурсивной части обобщенных табличных выражений применяются для задания текущего уровня рекурсии, а не для задания обобщенных табличных выражений.Analytic and aggregate functions in the recursive part of the CTE are applied to the set for the current recursion level and not to the set for the CTE. Такие функции, как ROW_NUMBER, работают только с подмножествами данных, которые передаются им текущим уровнем рекурсии, но не со всем множеством данных, которые передаются в рекурсивную часть обобщенного табличного выражения.Functions like ROW_NUMBER operate only on the subset of data passed to them by the current recursion level and not the entire set of data passed to the recursive part of the CTE. Дополнительные сведения см. в примере "Л. Использование аналитических функций в рекурсивном ОТВ" ниже.For more information, see example K. Using analytical functions in a recursive CTE that follows.

Возможности и ограничения общих табличных выражений в Хранилище данных SQLSQL Data Warehouse и Параллельное хранилище данныхParallel Data WarehouseFeatures and Limitations of Common Table Expressions in Хранилище данных SQLSQL Data Warehouse and Параллельное хранилище данныхParallel Data Warehouse

Текущая реализация обобщенных табличных выражений в Хранилище данных SQLSQL Data Warehouse и Параллельное хранилище данныхParallel Data Warehouse имеет следующие возможности и ограничения:The current implementation of CTEs in Хранилище данных SQLSQL Data Warehouse and Параллельное хранилище данныхParallel Data Warehouse have the following features and limitations:

  • Обобщенное табличное выражение можно задать в инструкции SELECT.A CTE can be specified in a SELECT statement.

  • Обобщенное табличное выражение можно задать в инструкции CREATE VIEW.A CTE can be specified in a CREATE VIEW statement.

  • Обобщенное табличное выражение можно задать в инструкции CREATE TABLE AS SELECT (CTAS).A CTE can be specified in a CREATE TABLE AS SELECT (CTAS) statement.

  • Обобщенное табличное выражение можно задать в инструкции CREATE REMOTE TABLE AS SELECT (CRTAS).A CTE can be specified in a CREATE REMOTE TABLE AS SELECT (CRTAS) statement.

  • Обобщенное табличное выражение можно задать в инструкции CREATE EXTERNAL TABLE AS SELECT (CETAS).A CTE can be specified in a CREATE EXTERNAL TABLE AS SELECT (CETAS) statement.

  • Обобщенное табличное выражение может ссылаться на внешнюю таблицу.A remote table can be referenced from a CTE.

  • Обобщенное табличное выражение может ссылаться на внешнюю таблицу.An external table can be referenced from a CTE.

  • В обобщенном табличном выражении можно задать несколько определений запросов обобщенных табличных выражений (ОТВ).Multiple CTE query definitions can be defined in a CTE.

  • За обобщенным табличным выражением должна следовать одиночная инструкция SELECT.A CTE must be followed by a single SELECT statement. Инструкции INSERT, UPDATE, DELETE, и MERGE не поддерживаются.INSERT, UPDATE, DELETE, and MERGE statements are not supported.

  • Обобщенное табличное выражение, которое включает ссылки на себя (рекурсивное обобщенное табличное выражение), не поддерживается.A common table expression that includes references to itself (a recursive common table expression) is not supported.

  • Задание в одном обобщенном табличном выражении нескольких предложений WITH недопустимо.Specifying more than one WITH clause in a CTE is not allowed. Например, если CTE_query_definition содержит вложенный запрос, этот вложенный запрос не может содержать вложенное предложение WITH, определяющее другое обобщенное табличное выражение.For example, if a CTE query definition contains a subquery, that subquery cannot contain a nested WITH clause that defines another CTE.

  • Предложение ORDER BY невозможно использовать в определении CTE_query_definition, кроме случаев, когда задано предложение TOP.An ORDER BY clause cannot be used in the CTE_query_definition, except when a TOP clause is specified.

  • Если обобщенное табличное выражение используется в инструкции, являющейся частью пакета, то за инструкцией, стоящей перед ней, должен следовать символ точки с запятой.When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.

  • При использовании в инструкциях, подготовленных методом sp_prepare, обобщенные табличные выражения будут вести себя так же, как другие инструкции SELECT в PDW.When used in statements prepared by sp_prepare, CTEs will behave the same way as other SELECT statements in PDW. Однако если обобщенные табличные выражения используются как часть инструкций CETAS, подготовленных методом sp_prepare, их поведение может отличаться от SQL ServerSQL Server и других инструкций PDW. Это обусловлено особенностями реализации привязки для метода sp_prepare.However, if CTEs are used as part of CETAS prepared by sp_prepare, the behavior can defer from SQL ServerSQL Server and other PDW statements because of the way binding is implemented for sp_prepare. Если инструкция SELECT, ссылающаяся на обобщенное табличное выражение, использует неправильный столбец, который отсутствует в CTE, метод sp_prepare будет передан без обнаружения ошибки, а ошибка вместо этого будет создана при выполнении метода sp_execute.If SELECT that references CTE is using a wrong column that does not exist in CTE, the sp_prepare will pass without detecting the error, but the error will be thrown during sp_execute instead.

ПримерыExamples

A.A. Создание простого обобщенного табличного выраженияCreating a simple common table expression

В следующем примере выводится общее количество заказов на продажу в год для каждого коммерческого представителя в Компания Adventure Works CyclesAdventure Works Cycles.The following example shows the total number of sales orders per year for each sales representative at Компания Adventure Works CyclesAdventure Works Cycles.

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

Б.B. Использование обобщенного табличного выражения для ограничения общего и среднего количества отчетовUsing a common table expression to limit counts and report averages

В следующем примере выводится среднее количество заказов на продажу за все годы для коммерческих представителей.The following example shows the average number of sales orders for all years for the sales representatives.

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;  

В.C. Использование нескольких определений ОТВ (обобщенных табличных выражений) в одном запросеUsing multiple CTE definitions in a single query

В следующем примере показано, как определить несколько ОТВ в одном запросе.The following example shows how to define more than one CTE in a single query. Обратите внимание, что для разделения определений запросов обобщенных табличных выражений используется запятая.Notice that a comma is used to separate the CTE query definitions. Функция FORMAT, используемая для отображения денежных сумм в формате валюты, доступна в SQL Server 2012 и более поздних версиях.The FORMAT function, used to display the monetary amounts in a currency format, is available in SQL Server 2012 and higher.

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','en-us') AS TotalSales  
  , SalesQuotaYear  
  , FORMAT (SalesQuota,'C','en-us') AS SalesQuota  
  , FORMAT (TotalSales -SalesQuota, 'C','en-us') 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;    

Здесь приводится частичный результирующий набор.Here is a partial result set.

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  

Г.D. Использование рекурсивного обобщенного табличного выражения для отображения нескольких уровней рекурсииUsing a recursive common table expression to display multiple levels of recursion

В следующем примере представлен иерархический список руководителей и служащих, отчитывающихся перед ними.The following example shows the hierarchical list of managers and the employees who report to them. Пример начинается с создания и заполнения таблицы dbo.MyEmployees.The example begins by creating and populating the dbo.MyEmployees table.

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

Д.E. Использование рекурсивного обобщенного табличного выражения для отображения двух уровней рекурсииUsing a recursive common table expression to display two levels of recursion

В следующем примере представлены руководители и отчитывающиеся перед ними служащие.The following example shows managers and the employees reporting to them. Количество возвращаемых уровней ограничено двумя.The number of levels returned is limited to two.

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 ;  

Е.F. Использование рекурсивного обобщенного табличного выражения для отображения иерархического спискаUsing a recursive common table expression to display a hierarchical list

Следующий пример строится на примере D с добавлением имен руководителей и служащих и соответствующих им должностей.The following example builds on Example D by adding the names of the manager and employees, and their respective titles. Иерархия руководителей и служащих дополнительно выделяется с помощью соответствующих отступов на каждом уровне.The hierarchy of managers and employees is additionally emphasized by indenting each level.

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;  

Ж.G. Использование подсказки MAXRECURSION для отмены инструкцииUsing MAXRECURSION to cancel a statement

Подсказка MAXRECURSION может использоваться для предотвращения входа в бесконечный цикл из-за неверно сформированного рекурсивного CTE-выражения.MAXRECURSION can be used to prevent a poorly formed recursive CTE from entering into an infinite loop. В следующем примере преднамеренно формируется бесконечный цикл и используется указание MAXRECURSION для ограничения числа уровней рекурсии двумя.The following example intentionally creates an infinite loop and uses the MAXRECURSION hint to limit the number of recursion levels to two.

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);  

После исправления ошибки в коде подсказка MAXRECURSION больше не нужна.After the coding error is corrected, MAXRECURSION is no longer required. В следующем примере приводится правильный код.The following example shows the corrected code.

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;  

З.H. Использование обобщенного табличного выражения для выборочного прохождения рекурсивной связи в инструкции SELECTUsing a common table expression to selectively step through a recursive relationship in a SELECT statement

В следующем примере показана иерархия узлов и компонентов продукции, необходимых для создания велосипеда для ProductAssemblyID = 800.The following example shows the hierarchy of product assemblies and components that are required to build the bicycle for 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;  

И.I. Использование рекурсивного обобщенного табличного выражения в инструкции UPDATEUsing a recursive CTE in an UPDATE statement

В следующем примере обновляется значение PerAssemblyQty для всех деталей, используемых при сборке продукта Road-550-W Yellow, 44 (ProductAssemblyID``800).The following example updates the PerAssemblyQty value for all parts that are used to build the product 'Road-550-W Yellow, 44' (ProductAssemblyID``800). Обобщенное табличное выражение возвращает иерархический список деталей, которые непосредственно используются для сборки ProductAssemblyID 800, и компонентов, которые используются для сборки этих деталей и т. д.The common table expression returns a hierarchical list of parts that are used to build ProductAssemblyID 800 and the components that are used to create those parts, and so on. Изменяются только строки, возвращенные обобщенным табличным выражением.Only the rows returned by the common table expression are modified.

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;  

К.J. Использование нескольких привязок и рекурсивных элементовUsing multiple anchor and recursive members

В следующем примере несколько членов указателя и рекурсивных элементов используются для возврата всех предков указанного лица.The following example uses multiple anchor and recursive members to return all the ancestors of a specified person. Создается и заполняется значениями таблица для формирования генеалогии семьи, возвращаемой рекурсивным обобщенным табличным выражением.A table is created and values inserted to establish the family genealogy returned by the recursive CTE.

-- 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  

K.K. Использование аналитических функций в рекурсивном обобщенном табличном выраженииUsing analytical functions in a recursive CTE

Следующий пример демонстрирует проблему, которая может возникнуть при использовании аналитической или агрегатной функции в рекурсивной части обобщенного табличного выражения.The following example shows a pitfall that can occur when using an analytical or aggregate function in the recursive part of a CTE.

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;  

Следующие результаты являются ожидаемыми результатами выполнения запроса.The following results are the expected results for the query.

Lvl  N  
1    0  
1    0  
1    0  
1    0  
2    4  
2    3  
2    2  
2    1  

Следующие результаты являются фактическими результатами выполнения запроса.The following results are the actual results for the query.

Lvl  N  
1    0  
1    0  
1    0  
1    0  
2    1  
2    1  
2    1  
2    1  

N возвращает 1 для каждого прохода рекурсивной части ОТВ, так как в ROWNUMBER передается только подмножество данных для данного уровня рекурсии.N returns 1 for each pass of the recursive part of the CTE because only the subset of data for that recursion level is passed to ROWNUMBER. Для каждой итерации рекурсивной части запроса в ROWNUMBER передается только одна строка.For each of the iterations of the recursive part of the query, only one row is passed to ROWNUMBER.

Примеры: Хранилище данных SQLSQL Data Warehouse и Параллельное хранилище данныхParallel Data WarehouseExamples: Хранилище данных SQLSQL Data Warehouse and Параллельное хранилище данныхParallel Data Warehouse

М.L. Использование обобщенного табличного выражения в инструкции CTASUsing a common table expression within a CTAS statement

В следующем примере создается новая таблица, содержащая общее количество заказов на продажу в год для каждого коммерческого представителя в Компания Adventure Works CyclesAdventure Works Cycles.The following example creates a new table containing the total number of sales orders per year for each sales representative at Компания Adventure Works CyclesAdventure Works Cycles.

USE AdventureWorks2012;  
GO   
CREATE TABLE SalesOrdersPerYear  
WITH  
(  
    DISTRIBUTION = HASH(SalesPersonID)  
)  
AS  
    -- 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  

Н.M. Использование обобщенного табличного выражения в инструкции CETASUsing a common table expression within a CETAS statement

В следующем примере создается новая внешняя таблица, содержащая общее количество заказов на продажу в год для каждого коммерческого представителя в Компания Adventure Works CyclesAdventure Works Cycles.The following example creates a new external table containing the total number of sales orders per year for each sales representative at Компания Adventure Works CyclesAdventure Works Cycles.

USE AdventureWorks2012;  
GO    
CREATE EXTERNAL TABLE SalesOrdersPerYear  
WITH  
(  
    LOCATION = 'hdfs://xxx.xxx.xxx.xxx:5000/files/Customer',  
    FORMAT_OPTIONS ( FIELD_TERMINATOR = '|' )   
)  
AS  
    -- 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  

О.N. Использование нескольких разделенных запятыми обобщенных табличных выражений в инструкцииUsing multiple comma separated CTEs in a statement

В следующем примере показано включение двух обобщенных табличных выражений в одну инструкцию.The following example demonstrates including two CTEs in a single statement. Обобщенные табличные выражения не поддерживают вложение (рекурсию).The CTEs cannot be nested (no recursion).

WITH   
 CountDate (TotalCount, TableName) AS  
    (  
     SELECT COUNT(datekey), 'DimDate' FROM DimDate  
    ) ,  
 CountCustomer (TotalAvg, TableName) AS  
    (  
     SELECT COUNT(CustomerKey), 'DimCustomer' FROM DimCustomer  
    )  
SELECT TableName, TotalCount FROM CountDate  
UNION ALL  
SELECT TableName, TotalAvg FROM CountCustomer;  

См. также:See Also

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