Підсумок щодо Common Table Expressions (CTE)

Ієрархічні та рекурсивні запити в SQL Server

Jasmin Ludolf

Content Developer

Структура курсу

Що таке рекурсія і як її використовувати?
Як використовувати Common Table Expressions (CTE) для ієрархічних і рекурсивних запитів?
Як подати ієрархію в SQL і як її запитувати?
Приклади з практики (напр., структура компанії, специфікація виробу, план польоту)

Зображення матрьошок і їх рекурсивної властивості.

Що таке CTE?

Визначення CTE

Задає тимчасовий іменований набір результатів, відомий як common table expression (CTE). Він походить від простого запиту і визначається в межах виконання однієї інструкції

Структура CTE

WITH CTEtable as 
(
    -- a query --
)

SELECT *
    FROM CTEtable

Використання CTE

Керувати складними запитами
Використовувати в операторах SELECT, INSERT, UPDATE або DELETE
Оголосити кілька CTE в одному операторі WITH
Поєднувати кілька CTE через UNION або JOIN
Заміна подання (view)
Самопосилання на таблицю
Рекурсивний запит

Створіть CTE для ІТ-організації

Виберіть менеджерів за допомогою CTE:

WITH JOBS (id, SurName, JobTitle) AS

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

WITH AS для визначення CTE
SELECT з таблиці Employee

Створіть CTE для ІТ-організації

Виберіть працівників із зарплатою понад 10 000

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

Створіть CTE для ІТ-організації

Увесь запит:

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