SQL Server CTE

В этом руководстве вы узнаете об общем табличном выражении(CTE) в SQL Server с использованием предложения WITH.

Введение в CTE в SQL Server

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

Ниже показан общий синтаксис CTE в SQL Server:

WITH expression_name[(column_name [,...])]
AS
   (CTE_definition)
SQL_statement;

В этом синтаксисе:

• Сначала укажите имя выражения(expression_name), на которое вы сможете ссылаться позже в запросе.
• Далее укажите список столбцов, разделенных запятыми, после expression_name. Количество столбцов должно быть таким же, как количество столбцов, определенное в CTE_definition.
• Затем используйте ключевое слово AS после имени выражения или списка столбцов, если указан список столбцов.
• После этого определите оператор SELECT, набор результатов которого заполняет общее табличное выражение.
• Наконец, обратитесь к общему табличному выражению в запросе(SQL_statement), например, SELECT, INSERT, UPDATE, DELETE или MERGE.

Мы предпочитаем использовать общие табличные выражения вместо использования подзапросов, поскольку общие табличные выражения более читабельны. Мы также используем CTE в запросах, которые содержат аналитические функции(или оконные функции )

Примеры CTE-выражений SQL Server

Давайте рассмотрим несколько примеров использования распространенных табличных выражений.

A) Простой пример SQL Server CTE

В этом запросе используется CTE для возврата объемов продаж по торговому персоналу в 2018 году:

WITH cte_sales_amounts(staff, sales, year) AS(
    SELECT    
        first_name + ' ' + last_name, 
        SUM(quantity * list_price *(1 - discount)),
        YEAR(order_date)
    FROM    
        sales.orders o
    INNER JOIN sales.order_items i ON i.order_id = o.order_id
    INNER JOIN sales.staffs s ON s.staff_id = o.staff_id
    GROUP BY 
        first_name + ' ' + last_name,
        year(order_date)
)
SELECT
    staff, 
    sales
FROM 
    cte_sales_amounts
WHERE
    year = 2018;

На следующем рисунке показан результирующий набор:

В этом примере:

• Сначала мы определили cte_sales_amounts как имя общего табличного выражения. CTE возвращает результат, состоящий из трех столбцов: staff, year и sales, полученных из запроса определения.
• Во-вторых, мы построили запрос, который возвращает общую сумму продаж по торговому персоналу и году, запрашивая данные из таблиц orders, order_items и staffs.
• В-третьих, мы ссылаемся на CTE во внешнем запросе и выбираем только те строки, год которых — 2018.

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

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

В этом примере CTE используется для возврата среднего количества заказов на продажу в 2018 году для всех сотрудников отдела продаж.

WITH cte_sales AS(
    SELECT 
        staff_id, 
        COUNT(*) order_count  
    FROM
        sales.orders
    WHERE 
        YEAR(order_date) = 2018
    GROUP BY
        staff_id
)
SELECT
    AVG(order_count) average_orders_by_staff
FROM 
    cte_sales;

Вот что получилось:

average_orders_by_staff
-----------------------
48
(1 row affected)

В этом примере:

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

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

SELECT    
    staff_id, 
    COUNT(*) order_count
FROM    
    sales.orders
WHERE 
    YEAR(order_date) = 2018
GROUP BY 
    staff_id;

В-третьих, мы ссылаемся на cte_sales во внешнем операторе и используем функцию AVG(), чтобы получить средний заказ продаж по всем сотрудникам.

SELECT
    AVG(order_count) average_orders_by_staff
FROM 
    cte_sales;

C) Пример использования нескольких SQL Server CTE в одном запросе

В следующем примере используются два CTE cte_category_counts и cte_category_sales для возврата количества продуктов и продаж для каждой категории продуктов. Внешний запрос объединяет два CTE с использованием столбца category_id.

WITH cte_category_counts(
    category_id, 
    category_name, 
    product_count
)
AS(
    SELECT 
        c.category_id, 
        c.category_name, 
        COUNT(p.product_id)
    FROM 
        production.products p
        INNER JOIN production.categories c 
            ON c.category_id = p.category_id
    GROUP BY 
        c.category_id, 
        c.category_name
),
cte_category_sales(category_id, sales) AS(
    SELECT    
        p.category_id, 
        SUM(i.quantity * i.list_price *(1 - i.discount))
    FROM    
        sales.order_items i
        INNER JOIN production.products p 
            ON p.product_id = i.product_id
        INNER JOIN sales.orders o 
            ON o.order_id = i.order_id
    WHERE order_status = 4 -- completed
    GROUP BY 
        p.category_id
) 
SELECT 
    c.category_id, 
    c.category_name, 
    c.product_count, 
    s.sales
FROM
    cte_category_counts c
    INNER JOIN cte_category_sales s 
        ON s.category_id = c.category_id
ORDER BY 
    c.category_name;

Вот результат:

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