Advanced SQL: введение в Common Table Expression (CTE)

Common Table Expression или CTE — это временно сгенерированная таблица данных, на которую SQL-запросы могут ссылаться с другими операторами, такими как SELECT. Первоначально он был представлен в 2005 году для SQL Server. Основная цель написания CTE — удобство повторных ссылок на него во всем наборе SQL-запросов. Это похоже на процесс рефакторинга в языке программирования. Сегодня мы хотели бы кратко представить CTE и то, как вы можете использовать его по-разному.

Синтаксис:

Вот краткий обзор синтаксиса:

WITH cte_table_name(column_1, column_2)
AS (
    SELECT column_1, column_2
    FROM table_1
)
SELECT * FROM cte_table_name

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

Пример одного CTE:

Вот пример для одного CTE, где мы выбрали каждый столбец из order_cte.

Несколько CTE:

Мы также могли бы создать несколько CTE и сослаться на них в следующем запросе. В следующем примере мы объединили два CTE вместе в столбце STORE_ID.

Результат запроса:

Рекурсивный CTE:

Рекурсивный CTE в основном создает CTE, который может ссылаться сам на себя. Он позволяет обрабатывать и создавать иерархические данные. Когда иерархические запросы недоступны для конкретного SQL-сервера, можно использовать рекурсивное CTE.

Здесь мы создали рекурсивное CTE, чтобы получить данные для организационной диаграммы, где следующее CTE содержит сотрудников, которыми управляют сотрудники из предыдущего CTE. Он будет работать до тех пор, пока сотрудники текущего CTE не будут управлять кем-либо. В дополнение к этому мы также использовали другой CTE, чтобы получить таблицу отделов, DEPT, так что мы также можем соединить имя отдела и расположение отдела с таблицей. Чтобы сохранить порядок рекурсивного CTE, в конце мы использовали LEFT JOIN.

Примечание: запрос будет выполняться рекурсивно на основе последнего CTE, а не объединенной таблицы.

Результат запроса:

Надеюсь, вы найдете это полезным! Спасибо, что прочитали совет этой недели!

Ознакомьтесь с другими советами по работе с данными на Dataproducts.io.