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

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

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure конечной точке аналитики платформы Аналитики Azure Synapse Analytics (PDW)в Microsoft FabricХранилище в базе данных Microsoft FabricSQL в Microsoft Fabric

Задается временно именованный результирующий набор, называемый обобщенным табличным выражением (ОТВ). Это является производным от простого запроса и определяется в пределах области выполнения одного SELECTоператора , или INSERTUPDATEMERGEDELETE оператора. Это предложение также можно использовать в инструкции как 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 <common_table_expression>, но expression_name может совпадать с именем базовой таблицы или представления. Любая ссылка на аргумент expression_name в запросе использует обобщенное табличное выражение, но не базовый объект.

column_name

Задается имя столбца в обобщенном табличном выражении. Повторяющиеся имена в определении одного обобщенного табличного выражения (CTE) не допускаются. Количество заданных имен столбцов должно совпадать с количеством столбцов в результирующем наборе CTE_query_definition. Список имен столбцов необязателен только в том случае, если всем результирующим столбцам в определении запроса присвоены уникальные имена.

CTE_query_definition

Указывает SELECT оператор, результирующий набор которого заполняет общее табличное выражение. Оператор SELECTдля CTE_query_definition должен соответствовать тем же требованиям, что и для создания представления, за исключением того, что CTE не может определить другой CTE. Дополнительные сведения см. в разделе "Примечания" и CREATE VIEW.

Если определено несколько CTE_query_definition, определения запросов должны быть присоединены одним из следующих операторов набора: UNION ALL, , UNIONEXCEPTили INTERSECT.

Рекомендации по использованию

Результаты запроса из распространенных выражений таблиц не материализуются. Каждая внешняя ссылка на именованный результирующий набор требует повторного выполнения определенного запроса. Для запросов, требующих нескольких ссылок на именованный результирующий набор, рекомендуется использовать временный объект .

Хранимую процедуру нельзя выполнить в общем табличном выражении.

Рекомендации по использованию рекурсивных и нерекурсивных ТС СМ. в следующих разделах.

Рекомендации по нерекурсивным общим табличным выражениям

Замечание

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

За CTE должен следовать одинSELECT, INSERTUPDATEMERGEили DELETE оператор, ссылающийся на некоторые или все столбцы CTE. Обобщенное табличное выражение может задаваться также в инструкции CREATE VIEW как часть определяющей инструкции SELECT представления.

Несколько определений запросов обобщенных табличных выражений (ОТВ) могут быть определены в нерекурсивных ОТВ. Определения могут объединяться одним из следующих операторов над множествами: UNION ALL, UNION, INTERSECT или EXCEPT.

Обобщенные табличные выражения (ОТВ) могут иметь ссылки на самих себя, а также на ОТВ, определенные до этого в том же предложении WITH. Ссылки на определяемые далее обобщенные табличные выражения недопустимы.

Задание в одном CTE нескольких предложений WITH недопустимо. Например, если CTE_query_definition содержит вложенный запрос, вложенный запрос не может содержать вложенное WITH предложение, определяющее другой CTE.

Дополнительные сведения о вложенных ЦТ в Microsoft Fabric см. в разделе Вложенные общие табличные выражения (CTE) в хранилище данных Fabric (Transact-SQL).

Следующие предложения не могут использоваться в CTE_query_definition:

  • ORDER BY (за исключением случаев, когда TOP указано предложение или OFFSET/FETCH предложение)
  • INTO
  • OPTION предложение с указанием запроса 1
  • FOR BROWSE

1 Предложение OPTION нельзя использовать в определении CTE. Его можно использовать только в самом SELECT внешнем операторе.

Если обобщенное табличное выражение используется в инструкции, являющейся частью пакета, то за инструкцией, стоящей перед ней, должен следовать символ точки с запятой.

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

В обобщенном табличном выражении могут быть ссылки на таблицы, находящиеся на удаленных серверах.

При выполнении CTE любые указания, ссылающиеся на CTE, могут конфликтовать с другими указаниями, обнаруженными, когда CTE обращается к своим базовым таблицам, таким же образом, как указания, ссылающиеся на представления в запросах. Когда это происходит, запрос возвращает ошибку.

Рекомендации по рекурсивным общим табличным выражениям

Замечание

Следующие рекомендации применяются к определению рекурсивного общего табличного выражения. Рекомендации, применимые к нерекурсивным ЦТ, см. в рекомендациях по нерекурсивным общим табличным выражениям.

Определение рекурсивного обобщенного табличного выражения должно содержать по крайней мере два определения обобщенного табличного выражения запросов — закрепленный элемент и рекурсивный элемент. Может быть определено несколько закрепленных элементов и рекурсивных элементов, однако все определения запросов закрепленного элемента должны быть поставлены перед первым определением рекурсивного элемента. Все определения обобщенных табличных выражений запросов (ОТВ) являются закрепленными элементами, если только они не ссылаются на само ОТВ.

Элементы привязки должны объединяться одним из следующих операторов набора: UNION ALL, , UNIONINTERSECTили EXCEPT. UNION ALL является единственным оператором набора, разрешенным между последним элементом привязки и первым рекурсивным элементом, а также при объединении нескольких рекурсивных элементов.

Количество столбцов членов указателя и рекурсивных элементов должно совпадать.

Тип данных столбца в рекурсивном элементе должен совпадать с типом данных соответствующего столбца в закрепленном элементе.

Предложение FROM рекурсивного элемента должно ссылаться на обобщенное табличное выражение expression_name только один раз.

Следующие элементы недопустимы в определении CTE_query_definition рекурсивного элемента:

  • SELECT DISTINCT
  • GROUP BY
  • PIVOT 1
  • HAVING
  • Скалярное агрегирование
  • TOP
  • LEFT, RIGHT, OUTER JOIN (INNER JOIN допускается)
  • Подзапросы
  • Указание, применимое к рекурсивной ссылке на обобщенное табличное выражение в определении CTE_query_definition.

1 . Если уровень совместимости базы данных равен 110 или выше. См. критические изменения функций ядра СУБД в SQL Server 2016.

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

  • Все столбцы, возвращаемые рекурсивным обобщенным табличным выражением, могут содержать значения NULL, независимо от того, могут ли иметь значения NULL столбцы, возвращаемые участвующими инструкциями SELECT.

  • Неправильно составленный рекурсивный CTE может привести к бесконечному циклу. Например, если определение запроса рекурсивного элемента возвращает одинаковые значения как для родительского, так и для дочернего столбца, то образуется бесконечный цикл. Чтобы предотвратить бесконечный цикл, можно ограничить количество уровней рекурсии, разрешенных для конкретной инструкции, с помощью MAXRECURSION указания и значения между 032767OPTION предложением , INSERTили UPDATE оператором DELETE. SELECT Это дает возможность контролировать выполнение инструкции до тех пор, пока не будет разрешена проблема с кодом, из-за которой происходит зацикливание программы. Серверное значение по умолчанию равно 100. Если указано значение 0, ограничения не применяются. В одной инструкции может быть указан только одно значение MAXRECURSION. Дополнительные сведения см. в подсказках к запросам.

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

  • Курсоры можно определить в запросах с помощью ТСЗ. Обобщенное табличное выражение является аргументом select_statement, который определяет результирующий набор курсора. Для рекурсивных обобщенных табличных выражений допустимы только однонаправленные и статические курсоры (курсоры моментального снимка). Если в рекурсивном обобщенном табличном выражении указан курсор другого типа, тип курсора преобразуется в статический.

  • В обобщенном табличном выражении могут быть ссылки на таблицы, находящиеся на удаленных серверах. Если на удаленный сервер имеются ссылки в рекурсивном элементе обобщенного табличного выражения, создается буфер для каждой удаленной таблицы, так что к таблицам может многократно осуществляться локальный доступ. Если это запрос CTE, индекс Spool/Lazy Spools отображаются в плане запроса и будут иметь дополнительный WITH STACK предикат. Это один из способов подтверждения надлежащей рекурсии.

  • Аналитические и агрегатные функции в рекурсивной части обобщенных табличных выражений применяются для задания текущего уровня рекурсии, а не для задания обобщенных табличных выражений. Такие функции, как ROW_NUMBER, работают только с подмножествами данных, которые передаются им текущим уровнем рекурсии, но не со всем множеством данных, которые передаются в рекурсивную часть обобщенного табличного выражения. Дополнительные сведения см. в примере "И". Использование аналитических функций в указанном ниже рекурсивном CTE.

Распространенные табличные выражения в Azure Synapse Analytics и analytics Platform System (PDW)

Текущая реализация CTEs в Azure Synapse Analytics и analytics Platform System (PDW) имеет следующие функции и требования:

  • Обобщенное табличное выражение можно задать в инструкции SELECT.

  • Обобщенное табличное выражение можно задать в инструкции CREATE VIEW.

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

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

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

  • Обобщенное табличное выражение может ссылаться на внешнюю таблицу.

  • Обобщенное табличное выражение может ссылаться на внешнюю таблицу.

  • В обобщенном табличном выражении можно задать несколько определений запросов обобщенных табличных выражений (ОТВ).

  • За CTE можно следовать операторам SELECT, INSERT, UPDATEDELETEили MERGE операторам.

  • Обобщенное табличное выражение, которое включает ссылки на себя (рекурсивное обобщенное табличное выражение), не поддерживается.

  • Задание в одном CTE нескольких предложений WITH недопустимо. Например, если CTE_query_definition содержит вложенный запрос, этот вложенный запрос не может содержать вложенное предложение WITH, определяющее другое обобщенное табличное выражение.

  • Предложение ORDER BY нельзя использовать в CTE_query_definition, за исключением случаев, когда TOP указано предложение.

  • Если обобщенное табличное выражение используется в инструкции, являющейся частью пакета, то за инструкцией, стоящей перед ней, должен следовать символ точки с запятой.

  • При использовании в инструкциях, подготовленных sp_prepareСТС, работают так же, как и другие SELECT инструкции в APS PDW. Однако если ТСЗ используются в рамках CETAS, подготовленнойsp_prepare, поведение может отложить от SQL Server и других инструкций APS PDW из-за способа реализации привязки.sp_prepare Если SELECT ссылка на CTE использует неправильный столбец, который не существует в CTE, sp_prepare проходит без обнаружения ошибки, но ошибка возникает во время sp_execute .

Примеры

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

В следующем примере показано общее количество заказов на продажу в год для каждого представителя по продажам в Adventure 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;

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

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

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;

В. Использование нескольких определений CTE в одном запросе

В следующем примере показано, как определить несколько ОТВ в одном запросе. Запятая используется для разделения определений запросов CTE. Функция, используемая FORMAT для отображения денежных сумм в формате валюты, была представлена в SQL Server 2012 (11.x).

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
     INNER JOIN Sales_Quota_CTE
         ON Sales_Quota_CTE.BusinessEntityID = Sales_CTE.SalesPersonID
        AND Sales_CTE.SalesYear = Sales_Quota_CTE.SalesQuotaYear
ORDER BY SalesPersonID, SalesYear;

Далее представлен частичный результирующий набор.

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 SMALLINT NULL,
    CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC),
    CONSTRAINT FK_MyEmployees_ManagerID_EmployeeID FOREIGN KEY (ManagerID) REFERENCES dbo.MyEmployees (EmployeeID)
);

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

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;

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

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

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;

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

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

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
         INNER JOIN DirectReports AS d
             ON e.ManagerID = d.EmployeeID
)
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM DirectReports
ORDER BY Sort;

Использование подсказки MAXRECURSION для отмены инструкции

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

--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
         INNER 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 ошибки кодирования больше не требуется. В следующем примере приводится правильный код.

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
         INNER JOIN cte
             ON e.ManagerID = cte.EmployeeID
)
SELECT EmployeeID, ManagerID, Title
FROM cte;

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

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

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

F. Использование рекурсивного CTE в инструкции UPDATE

В следующем примере обновляется PerAssemblyQty значение для всех частей, используемых для сборки продукта 'Road-550-W Yellow, 44' (ProductAssemblyID 800). Обобщенное табличное выражение возвращает иерархический список деталей, которые непосредственно используются для сборки ProductAssemblyID 800, и компонентов, которые используются для сборки этих деталей и т. д. Изменяются только строки, возвращенные обобщенным табличным выражением.

USE AdventureWorks2022;
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
     INNER JOIN Parts AS d
         ON c.ProductAssemblyID = d.AssemblyID
WHERE d.ComponentLevel = 0;

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

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

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

И. Использование аналитических функций в рекурсивном 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
         INNER 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 передается только одна строка.

Примеры: Azure Synapse Analytics и система платформы аналитики (PDW)

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

В следующем примере создается новая таблица, содержащая общее количество заказов на продажу в год для каждого представителя продаж в Adventure Works Cycles.

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

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

В следующем примере создается новая внешняя таблица, содержащая общее количество заказов на продажу в год для каждого представителя по продажам в Adventure Works Cycles.

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

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

В следующем примере показано включение двух обобщенных табличных выражений в одну инструкцию. Обобщенные табличные выражения не поддерживают вложение (рекурсию).

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;

Связанный контент