A иерархический запрос - нет pe из SQL-запроса, который обрабатывает данные иерархической модели. Это частные случаи более общих рекурсивных запросов фиксированной точки, которые вычисляют транзитивные замыкания.
В стандартном SQL: 1999 иерархические запросы реализованы посредством рекурсивных общих табличных выражений ( CTE). В отличие от более раннего предложения Oracle connect-by, рекурсивные CTE с самого начала разрабатывались с семантикой fixpoint. Рекурсивные CTE из стандарта были относительно близки к существующей реализации в IBM DB2 версии 2. Рекурсивные CTE также поддерживаются Microsoft SQL Server (начиная с SQL Server 2008 R2), Firebird 2.1, PostgreSQL 8.4+, SQLite 3.8.3+, IBM Informix версия 11.50+, CUBRID, MariaDB 10.2+ и MySQL 8.0.1+,. В Tableau есть документация, описывающая, как можно использовать CTE. TIBCO Spotfire не поддерживает CTE, а в реализации Oracle 11g Release 2 отсутствует семантика фиксированных точек.
Без общих табличных выражений или связанных предложений можно выполнять иерархические запросы с определяемыми пользователем рекурсивными функциями.
A общее табличное выражение или CTE (в SQL ) - это временный именованный набор результатов, полученный из простого запроса и определенный в пределах области выполнения SELECT
, INSERT
, UPDATE
или DELETE
инструкция.
CTE можно рассматривать как альтернативу производным таблицам (подзапрос ), представлениям и встроенным пользовательским функциям.
Общие табличные выражения поддерживаются Teradata, DB2, Informix (начиная с версии 14.1), Firebird, Microsoft SQL Server, Oracle (с рекурсией, начиная с выпуска 2 11g), PostgreSQL (начиная с 8.4), MariaDB (с 10.2), MySQL (начиная с 8.0), SQLite (начиная с 3.8.3), HyperSQL и H2 (экспериментально). Oracle называет CTE «факторингом подзапроса».
Синтаксис рекурсивного CTE выглядит следующим образом:
WITH [RECURSIVE] with_query [,...] SELECT...
где with_query Синтаксис
:
имя_запроса [(имя_столбца [,...])] AS (SELECT...)
Рекурсивные CTE (или "рекурсивный факторинг подзапроса" на жаргоне Oracle) могут использоваться для обходить отношения (в виде графиков или деревьев), хотя синтаксис намного сложнее, потому что не создаются автоматические псевдостолбцы (например, LEVEL
ниже ); если они желательны, они должны быть созданы в коде. См. Документацию MSDN или документацию IBM для примеров руководств.
Ключевое слово RECURSIVE
обычно не требуется после WITH в системах, отличных от PostgreSQL.
В SQL: 1999 рекурсивный запрос (CTE) может появляться везде, где запрос разрешен. Можно, например, назвать результат с помощью CREATE
[RECURSIVE
] VIEW
. Используя CTE внутри INSERT INTO
, можно заполнить таблицу данными, созданными из рекурсивного запроса; Генерация случайных данных возможна с использованием этого метода без использования каких-либо процедурных операторов.
Некоторые базы данных, такие как PostgreSQL, поддерживают более короткий формат CREATE RECURSIVE VIEW, который внутренне переведен в кодирование WITH RECURSIVE.
Пример рекурсивного запроса, вычисляющего факториал чисел от 0 до 9, выглядит следующим образом:
WITH RECURSIVE temp (n, fact) AS (SELECT 0, 1 - Initial Subquery UNION ALL SELECT n + 1, (n + 1) * fact FROM temp - Рекурсивный подзапрос WHERE n < 9) SELECT * FROM temp;
Альтернативным синтаксисом является нестандартная конструкция CONNECT BY
; она была введена Oracle в 1980-х годах. До Oracle 10g конструкция была полезна только для обхода ациклических графов, потому что она возвращала ошибку при обнаружении любых циклов; в версии 10g Oracle представила функцию NOCYCLE (и ключевое слово), заставляя обход работать при наличии циклов также.
CONNECT BY
поддерживается EnterpriseDB, Oracle database, CUBRID, IBM Informix и DB2, но только если он включен как режим совместимости. Синтаксис следующий:
SELECT select_list FROM table_expression [WHERE...] [НАЧАТЬ С начального_выражения] CONNECT BY [NOCYCLE] {PRIOR child_expr = parent_expr | parent_expr = PRIOR child_expr} [ЗАКАЗАТЬ SIBLINGS ПО столбцу1 [ASC | DESC] [, столбец2 [ASC | DESC]]...] [GROUP BY...] [HAVING...]...
SELECT LEVEL, LPAD ('', 2 * (LEVEL - 1)) || ename "сотрудник", empno, mgr "менеджер" ИЗ emp НАЧАТЬ С mgr НУЛЕНО ПОДКЛЮЧИТЬСЯ ПО ПРИОРУ empno = mgr;
Результат вышеуказанного запроса будет выглядеть так:
level | сотрудник | empno | менеджер ------- + ------------- + ------- + --------- 1 | КОРОЛЬ | 7839 | 2 | ДЖОНС | 7566 | 7839 3 | СКОТТ | 7788 | 7566 4 | АДАМС | 7876 | 7788 3 | FORD | 7902 | 7566 4 | СМИТ | 7369 | 7902 2 | БЛЕЙК | 7698 | 7839 3 | АЛЛЕН | 7499 | 7698 3 | WARD | 7521 | 7698 3 | МАРТИН | 7654 | 7698 3 | ТЕРНЕР | 7844 | 7698 3 | ДЖЕЙМС | 7900 | 7698 2 | КЛАРК | 7782 | 7839 3 | МИЛЛЕР | 7934 | 7782 (14 строк)
В следующем примере возвращается фамилия каждого сотрудник в отделе 10, каждый менеджер выше этого сотрудника в иерархии, количество уровней между руководителем и сотрудником и путь между ними:
SELECT ename «Employee», CONNECT_BY_ROOT ename «Manager», LEVEL-1 «Pathlen» ", SYS_CONNECT_BY_PATH (ename, '/')" Путь "ОТ emp ГДЕ УРОВЕНЬ>1 и deptno = 10 ПОДКЛЮЧЕНИЕ ПО ПРИОРУ empno = mgr ЗАКАЗАТЬ ПО" Сотруднику "," Менеджеру "," Пути "," Пути ";
SYS_CONNECT_BY_PATH
Академические учебники . Обратите внимание, что они охватывают только стандарт SQL: 1999 (и журнал данных), но не расширение Oracle.