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

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

Общие сведения о синтаксисе CTE

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

WITH CTE_Name (Column1, Column2)
AS
(
    -- CTE query definition
    SELECT Column1, Column2
    FROM SomeTable
    WHERE SomeCondition = 'Value'
)
SELECT * FROM CTE_Name;

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

• Улучшенная удобочитаемость: сложные запросы становятся проще для понимания, когда их разбивают на именованные логические разделы
• Возможность самостоятельного обращения к данным: рекурсивные ТС могут ссылаться на себя, что позволяет выполнять иерархический обход данных.
• Несколько ссылок: ссылка на один И тот же CTE несколько раз во внешнем запросе без переопределения
• Модульное проектирование: пошагово создавайте сложные запросы путем определения нескольких общих табличных выражений (ТС)

Создание нерекурсивных ТС

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

В следующем примере CTE используется для вычисления метрик продаж перед присоединением к сведениям о продукте:

WITH SalesSummary AS
(
    SELECT 
        ProductID,
        SUM(OrderQty) AS TotalQuantity,
        SUM(LineTotal) AS TotalRevenue,
        COUNT(DISTINCT SalesOrderID) AS OrderCount
    FROM SalesLT.SalesOrderDetail
    GROUP BY ProductID
)
SELECT 
    p.Name AS ProductName,
    p.ProductNumber,
    p.ListPrice,
    ss.TotalQuantity,
    ss.TotalRevenue,
    ss.OrderCount,
    ss.TotalRevenue / NULLIF(ss.TotalQuantity, 0) AS AverageUnitPrice
FROM SalesLT.Product AS p
INNER JOIN SalesSummary AS ss
    ON p.ProductID = ss.ProductID
ORDER BY ss.TotalRevenue DESC;

Этот запрос сначала создает CTE с именем SalesSummary , который объединяет сведения о заказе по продукту, вычисляя общее количество проданных, общий доход и количество заказов. Затем основной запрос присоединяет этот CTE к Product таблице, чтобы отобразить имена продуктов вместе с их метриками продаж. Функция NULLIF предотвращает деление на ноль при расчете средней цены на единицу.

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

WITH CategorySales AS
(
    SELECT 
        p.ProductCategoryID,
        SUM(sod.LineTotal) AS CategoryRevenue
    FROM SalesLT.Product AS p
    INNER JOIN SalesLT.SalesOrderDetail AS sod
        ON p.ProductID = sod.ProductID
    GROUP BY p.ProductCategoryID
),
RankedCategories AS
(
    SELECT 
        ProductCategoryID,
        CategoryRevenue,
        RANK() OVER (ORDER BY CategoryRevenue DESC) AS RevenueRank
    FROM CategorySales
)
SELECT 
    pc.Name AS CategoryName,
    rc.CategoryRevenue,
    rc.RevenueRank
FROM RankedCategories AS rc
INNER JOIN SalesLT.ProductCategory AS pc
    ON rc.ProductCategoryID = pc.ProductCategoryID
WHERE rc.RevenueRank <= 5;

Создайте рекурсивные СКВ

Рекурсивные выражения общего табличного выражения (CTE) обслуживают структуру данных, подобную иерархической или графовой, обращаясь к самим себе. Рекурсивный CTE состоит из двух частей: якорного элемента, предоставляющего начальный результирующий набор, и рекурсивного члена, ссылающегося на CTE для наращивания предыдущих результатов.

Общий синтаксис рекурсивного CTE:

WITH RecursiveCTE AS
(
    -- Anchor member: starting point
    SELECT columns
    FROM table
    WHERE starting_condition

    UNION ALL

    -- Recursive member: references the CTE
    SELECT columns
    FROM table
    INNER JOIN RecursiveCTE
        ON join_condition
)
SELECT * FROM RecursiveCTE;

Распространенный вариант использования — обход иерархии организации. Рассмотрим таблицу сотрудников, в которой каждый сотрудник имеет руководителя:

WITH EmployeeHierarchy AS
(
    -- Anchor: Start with top-level managers (no manager)
    SELECT 
        EmployeeID,
        FirstName,
        LastName,
        ManagerID,
        0 AS Level,
        CAST(FirstName + ' ' + LastName AS NVARCHAR(500)) AS HierarchyPath
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL

    UNION ALL

    -- Recursive: Find employees who report to previously found employees
    SELECT 
        e.EmployeeID,
        e.FirstName,
        e.LastName,
        e.ManagerID,
        eh.Level + 1,
        CAST(eh.HierarchyPath + ' > ' + e.FirstName + ' ' + e.LastName AS NVARCHAR(500))
    FROM HumanResources.Employee AS e
    INNER JOIN EmployeeHierarchy AS eh
        ON e.ManagerID = eh.EmployeeID
)
SELECT 
    EmployeeID,
    FirstName,
    LastName,
    Level,
    HierarchyPath
FROM EmployeeHierarchy
ORDER BY HierarchyPath;

Создание последовательностей с рекурсивными ТС

Рекурсивные ТСВ преуспевают в создании последовательностей чисел или дат без необходимости в наличии таблицы чисел. Этот метод полезен для создания диапазонов дат, заполнения пробелов в данных или создания тестовых данных:

-- Generate a sequence of dates for the current month
WITH DateSequence AS
(
    -- Anchor: Get the first day of the current month
    SELECT CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS DATE) AS DateValue

    UNION ALL

    -- Recursive: Add one day until we reach the end of the month
    SELECT DATEADD(DAY, 1, DateValue)
    FROM DateSequence
    WHERE DateValue < EOMONTH(GETDATE())
)
-- Output each date with its day name
SELECT DateValue, DATENAME(WEEKDAY, DateValue) AS DayName
FROM DateSequence
OPTION (MAXRECURSION 31);  -- Allow up to 31 iterations (max days in a month)

Вы можете объединить созданные последовательности с фактическими данными, чтобы определить пробелы или создать сводные отчеты:

-- Generate a numbers table from 1 to 1000
WITH Numbers AS
(
    -- Anchor: Start with 1
    SELECT 1 AS n
    UNION ALL
    -- Recursive: Increment until we reach 1000
    SELECT n + 1 FROM Numbers WHERE n < 1000
),
-- Convert numbers to dates for the entire year
DateRange AS
(
    SELECT DATEADD(DAY, n - 1, '2024-01-01') AS OrderDate
    FROM Numbers
    WHERE DATEADD(DAY, n - 1, '2024-01-01') <= '2024-12-31'
)
-- Count orders for each date, showing 0 for dates with no orders
SELECT 
    dr.OrderDate,
    COALESCE(COUNT(soh.SalesOrderID), 0) AS OrderCount
FROM DateRange AS dr
LEFT JOIN SalesLT.SalesOrderHeader AS soh
    ON CAST(soh.OrderDate AS DATE) = dr.OrderDate
GROUP BY dr.OrderDate
ORDER BY dr.OrderDate
OPTION (MAXRECURSION 366);  -- Allow up to 366 iterations (leap year)

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

CTE можно использовать с INSERT, UPDATE и DELETE операторами, а не только с SELECT. Эта возможность полезна, если логика изменения требует сложной фильтрации или вычислений:

-- Update using a CTE to identify target rows
WITH DiscontinuedProducts AS
(
    SELECT ProductID
    FROM SalesLT.Product
    WHERE SellEndDate < DATEADD(YEAR, -2, GETDATE())
        AND ProductID NOT IN (
            SELECT DISTINCT ProductID 
            FROM SalesLT.SalesOrderDetail
            WHERE ModifiedDate > DATEADD(YEAR, -1, GETDATE())
        )
)
UPDATE SalesLT.Product
SET DiscontinuedDate = GETDATE()
WHERE ProductID IN (SELECT ProductID FROM DiscontinuedProducts);

Этот запрос использует CTE для идентификации продуктов, которые должны быть помечены как прекращенные. CTE находит продукты, где дата окончания продажи более двух лет назад, и которые не появились в каких-либо деталях заказа, измененных в течение прошлого года. Затем инструкция UPDATE задает DiscontinuedDate для этих продуктов. Разделив логику выбора на CTE, запрос становится проще читать и тестировать независимо.

Дополнительные сведения о распространенных выражениях таблиц см. в разделе WITH common_table_expression (Transact-SQL) и рекурсивные запросы с использованием общих выражений таблицы.