Повторение общих табличных выражений (CTE)

Иерархические и рекурсивные запросы в SQL Server

Jasmin Ludolf

Content Developer

Обзор курса

Что такое рекурсия и как её применять?
Как использовать общие табличные выражения (CTE) для иерархических и рекурсивных запросов?
Как представить иерархию в SQL и как выполнять запросы к ней?
Примеры из практики (например, структура компании, спецификация материалов, план полёта)

Изображение матрёшек и их рекурсивное свойство.

Что такое CTE?

Определение CTE

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

Структура CTE

WITH CTEtable as 
(
    -- a query --
)

SELECT *
    FROM CTEtable

Применение CTE

Упрощение сложных запросов
Можно применять в операторах SELECT, INSERT, UPDATE или DELETE
Несколько CTE можно определить в одном операторе WITH
Объединение нескольких CTE с помощью UNION или JOIN
Замена представления
Самоссылка на таблицу
Рекурсивный запрос

Определение CTE для IT-организации

Выборка менеджеров с помощью CTE:

WITH JOBS (id, SurName, JobTitle) AS

(
    SELECT 
          ID, 
          Name,
          Position
    FROM employee
    WHERE Position like '%Manager%'
),

WITH AS для определения CTE
SELECT из таблицы Employee

Определение CTE для IT-организации

Выборка сотрудников с зарплатой свыше 10 000

SALARIES (ID,Salary) AS (
   SELECT
        ID,
        Salary
   FROM ITSalary
   WHERE Salary > 10000)

Определение CTE для IT-организации

Полный запрос:

WITH JOBS (id, SurName, JobTitle) AS 
(    SELECT ID, Name, Position
        FROM employee 
        WHERE Position like '%Manager%'),

SALARIES (ID,Salary) AS
(    SELECT ID, Salary
        FROM ITSalary 
        WHERE Salary > 10000)

SELECT JOBS.Name, JOBS.Position, SALARIES.Salary
    FROM JOBS
    INNER JOIN SALARIES
    on JOBS.ID = SALARIES.ID;

SurNmames     | JobTitle          | Salary
Paul Smith    | IT Manager        | 15,000
Adam Peterson | Sourcing Manager  | 12,500
Anna Nilson   | Portfolio Manager | 10,500

Давайте потренируемся!

Иерархические и рекурсивные запросы в SQL Server