Иерархический запрос представляет собой тип SQL запроса , который обрабатывает иерархическую модель данных. Это частные случаи более общих рекурсивных запросов фиксированной точки, которые вычисляют транзитивные замыкания .
В стандартном SQL: 1999 иерархические запросы реализованы посредством рекурсивных общих табличных выражений (CTE). В отличие от более раннего предложения Oracle connect-by , рекурсивные CTE с самого начала проектировались с семантикой фиксированных точек . [1] Рекурсивные CTE из стандарта были относительно близки к существующей реализации в IBM DB2 версии 2. [1] Рекурсивные CTE также поддерживаются Microsoft SQL Server (начиная с SQL Server 2008 R2), [2] Firebird 2.1 , [3] PostgreSQL 8.4+ , [4] SQLite 3.8.3+ , [5] IBM Informixверсия 11.50+, CUBRID , MariaDB 10.2+ и MySQL 8.0.1+ . [6] В Tableau есть документация, описывающая, как можно использовать CTE. TIBCO Spotfire не поддерживает CTE, а в реализации Oracle 11g Release 2 отсутствует семантика фиксированных точек.
Без общих табличных выражений или связанных предложений можно выполнять иерархические запросы с определяемыми пользователем рекурсивными функциями. [7]
Общее табличное выражение
Общее выражение таблицы или КТР, (в SQL ) является временным назван результирующий набор, полученный из простого запроса и определяется в пределах выполнения сферы действия SELECT
, INSERT
, UPDATE
или DELETE
заявления.
CTE можно рассматривать как альтернативу производным таблицам ( подзапросам ), представлениям и встроенным пользовательским функциям.
Общие табличные выражения поддерживаются Teradata (начиная с версии 14), DB2 , Informix (начиная с версии 14.1), Firebird (начиная с версии 2.1), [8] Microsoft SQL Server (начиная с версии 2005), Oracle (с рекурсией, начиная с 11g, выпуск 2), PostgreSQL (с 8.4), MariaDB (с 10.2), MySQL (с 8.0), SQLite (с 3.8.3), HyperSQL , Informix (с 14.10), [9] Google BigQuery , Sybase (начиная с версии 9), Vertica , H2 (экспериментальный), [10] и многие другие . Oracle называет CTE «факторингом подзапросов». [11]
Синтаксис CTE (который может быть рекурсивным, а может и не быть) выглядит следующим образом:
С [ RECURSIVE ] with_query [, ...] ВЫБРАТЬ ...
где with_query
синтаксис:
имя_запроса [ ( имя_столбца [, ...]) ] КАК ( ВЫБРАТЬ ...)
Рекурсивные CTE могут использоваться для обхода отношений (в виде графиков или деревьев), хотя синтаксис намного сложнее, потому что не создаются автоматические псевдостолбцы (как LEVEL
показано ниже ); если они желательны, они должны быть созданы в коде. См. Документацию MSDN [2] или документацию IBM [12] [13] для ознакомления с примерами руководств.
RECURSIVE
Ключевое слово обычно не требуется после того, как в рамках других , чем PostgreSQL систем. [14]
В SQL: 1999 рекурсивный (CTE) запрос может появляться везде, где разрешен запрос. Например, можно назвать результат с помощью CREATE
[ RECURSIVE
] VIEW
. [15] Используя CTE внутри INSERT INTO
, можно заполнить таблицу данными, сгенерированными из рекурсивного запроса; Генерация случайных данных возможна с использованием этого метода без использования каких-либо процедурных инструкций. [16]
Некоторые базы данных, такие как PostgreSQL, поддерживают более короткий формат CREATE RECURSIVE VIEW, который внутренне переведен в кодирование WITH RECURSIVE. [17]
Пример рекурсивного запроса, вычисляющего факториал чисел от 0 до 9, следующий:
WITH RECURSIVE temp ( n , fact ) AS ( SELECT 0 , 1 - Исходный подзапрос UNION ALL SELECT n + 1 , ( n + 1 ) * fact FROM temp - Рекурсивный подзапрос WHERE n < 9 ) SELECT * FROM temp ;
ПОДКЛЮЧИТЬСЯ
Альтернативный синтаксис - нестандартная CONNECT BY
конструкция; он был представлен Oracle в 1980-х годах. [18] До Oracle 10g конструкция была полезна только для обхода ациклических графов, потому что она возвращала ошибку при обнаружении любых циклов; в версии 10g Oracle представила функцию NOCYCLE (и ключевое слово), благодаря которой обход работает также и при наличии циклов. [19]
CONNECT BY
поддерживается Snowflake , EnterpriseDB , [20] базой данных Oracle , [21] CUBRID , [22] IBM Informix [23] и DB2, но только если он включен в качестве режима совместимости. [24] Синтаксис следующий:
ВЫБРАТЬ список_выборов ИЗ выражение_таблицы [ ГДЕ ... ] [ НАЧАТЬ С начального_выражения ] ПОДКЛЮЧИТЬСЯ ПО [ NOCYCLE ] { ПРИОР child_expr = parent_expr | parent_expr = PRIOR child_expr } [ ЗАКАЗАТЬ SIBLINGS ПО столбцу1 [ ASC | DESC ] [, столбец2 [ ASC | DESC ] ] ... ] [ ГРУППА ПО ... ] [ ИМЕЕТ ... ] ...
- Например,
ВЫБРАТЬ УРОВЕНЬ , LPAD ( '' , 2 * ( УРОВЕНЬ - 1 )) || ENAME "Сотрудник" , EMPNO , мгры "менеджер" ОТ EMP START С Диспом IS NULL CONNECT BY ПРИОРОМ EMPNO = мгры ;
Результат вышеуказанного запроса будет выглядеть так:
уровень | сотрудник | 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 рядов)
Псевдоколонки
- УРОВЕНЬ
- CONNECT_BY_ISLEAF
- CONNECT_BY_ISCYCLE
- CONNECT_BY_ROOT
Унарные операторы
В следующем примере возвращается фамилия каждого сотрудника в отделе 10, каждого менеджера выше этого сотрудника в иерархии, количество уровней между менеджером и сотрудником и путь между ними:
SELECT ename "Employee" , CONNECT_BY_ROOT ename "Manager" , LEVEL - 1 "Pathlen" , SYS_CONNECT_BY_PATH ( ename , '/' ) "Path" FROM emp WHERE LEVEL > 1 и deptno = 10 CONNECT BY PRIOR empno = mgr ORDER BY "Employee " , " Менеджер " , " Патлен " , " Путь " ;
Функции
SYS_CONNECT_BY_PATH
Смотрите также
- Datalog также реализует запросы фиксированных точек.
- Дедуктивные базы данных
- Иерархическая модель
- Достижимость
- Переходное закрытие
- Древовидная структура
Рекомендации
- ^ a b Джим Мелтон; Алан Р. Саймон (2002). SQL: 1999: Понимание компонентов реляционного языка . Морган Кауфманн. ISBN 978-1-55860-456-8.
- ^ а б Microsoft. «Рекурсивные запросы с использованием общих табличных выражений» . Проверено 23 декабря 2009 .
- ^ Хелен Борри (2008-07-15). «Примечания к выпуску Firebird 2.1» . Проверено 24 ноября 2015 .
- ^ «С запросами» . PostgreSQL
- ^ «С пунктом» . SQLite
- ^ "MySQL 8.0 Labs: [Рекурсивные] общие табличные выражения в MySQL (CTE)" . mysqlserverteam.com
- ^ Paragon corporation: Использование пользовательских функций PostgreSQL для решения проблемы дерева , 15 февраля 2004 г., по состоянию на 19 сентября 2015 г.
- ^ https://firebirdsql.org/file/documentation/reference_manuals/reference_material/Firebird-2.5-LangRef-Update.pdf
- ^ возможно до 14.10 с временными таблицами https://stackoverflow.com/questions/42579298/why-does-a-with-clause-give-a-syntax-error-on-informix
- ^ http://www.h2database.com/html/advanced.html#recursive_queries
- ^ Карен Мортон; Робин Сэндс; Джаред Стилл; Риядж Шамсудин; Керри Осборн (2010). Профессиональный Oracle SQL . Апресс. п. 283. ISBN. 978-1-4302-3228-5.
- ^ http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2z9.doc.apsg/src/tpc/db2z_xmprecursivecte.htm
- ^ http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=%2Fsqlp%2Frbafyrecursivequeries.htm
- ^ Регина Обе; Лео Сюй (2012). PostgreSQL: готово и работает . O'Reilly Media. п. 94. ISBN 978-1-4493-2633-3.
- ^ Джим Мелтон; Алан Р. Саймон (2002). SQL: 1999: Понимание компонентов реляционного языка . Морган Кауфманн. п. 352. ISBN. 978-1-55860-456-8.
- ^ Дон Чемберлин (1998). Полное руководство по DB2 Universal Database . Морган Кауфманн. С. 253–254. ISBN 978-1-55860-482-7.
- ^ https://www.postgresql.org/docs/10/static/sql-createview.html
- ^ Бенедикт, М .; Сенелларт, П. (2011). «Базы данных». В Blum, Edward K .; Ахо, Альфред В. (ред.). Информатика. Аппаратное обеспечение, программное обеспечение и его суть . п. 189. DOI : 10.1007 / 978-1-4614-1168-0_10 . ISBN 978-1-4614-1167-3.
- ^ Санджай Мишра; Алан Больё (2004). Освоение Oracle SQL . O'Reilly Media, Inc. стр. 227. ISBN 978-0-596-00632-7.
- ^ Иерархические запросы, заархивированные 21.06.2008 на Wayback Machine , EnterpriseDB
- ^ Иерархические запросы , Oracle
- ^ «Иерархический запрос CUBRID» . Проверено 11 февраля 2013 года .
- ^ Иерархическая оговорка , IBM Informix
- ^ Джонатан Генник (2010). Карманное руководство по SQL (3-е изд.). O'Reilly Media, Inc. стр. 8. ISBN 978-1-4493-9409-7.
дальнейшее чтение
- CJ Date (2011). SQL и теория отношений: как писать точный код SQL (2-е изд.). O'Reilly Media. С. 159–163. ISBN 978-1-4493-1640-2.
Академические учебники . Обратите внимание, что они охватывают только стандарт SQL: 1999 (и журнал данных), но не расширение Oracle.
- Авраам Зильбершатц; Генри Корт; С. Сударшан (2010). Концепции системы баз данных (6-е изд.). Макгроу-Хилл. С. 187–192. ISBN 978-0-07-352332-3.
- Рагху Рамакришнан; Йоханнес Герке (2003). Системы управления базами данных (3-е изд.). Макгроу-Хилл. ISBN 978-0-07-246563-1. Глава 24.
- Эктор Гарсиа-Молина; Джеффри Д. Уллман; Дженнифер Видом (2009). Системы баз данных: полная книга (2-е изд.). Пирсон Прентис Холл. С. 437–445. ISBN 978-0-13-187325-4.
Внешние ссылки
- https://stackoverflow.com/questions/1731889/cycle-detection-with-recursive-subquery-factoring
- http://explainextended.com/2009/11/18/sql-server-are-the-recursive-ctes-really-set-based/
- https://web.archive.org/web/20131114094211/http://gennick.com/with.html
- http://www.cs.duke.edu/courses/fall04/cps116/lectures/11-recursion.pdf
- http://www.blacktdn.com.br/2015/06/blacktdn-mssql-usando-consulta-cte.html