Из Википедии, бесплатной энциклопедии
  (Перенаправлено из With (SQL) )
Перейти к навигации Перейти к поиску

Иерархический запрос представляет собой тип 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 , DB2 , Informix (начиная с версии 14.1), Firebird , [8] Microsoft SQL Server , Oracle (с рекурсией, начиная с выпуска 2 11g), PostgreSQL (начиная с 8.4), MariaDB (с 10.2), MySQL (начиная с 8.0), SQLite (начиная с 3.8.3), HyperSQL и H2 (экспериментально). [9] Oracle называет CTE «факторингом подзапросов». [10]

Синтаксис рекурсивного CTE следующий:

С  [ RECURSIVE ]  with_query  [,  ...] ВЫБРАТЬ ...

где with_queryсинтаксис:

имя_запроса  [  ( имя_столбца  [, ...])  ]  КАК  ( ВЫБРАТЬ  ...)

Рекурсивные CTE (или «рекурсивный факторинг подзапросов» [11] на жаргоне Oracle) могут использоваться для обхода отношений (в виде графиков или деревьев), хотя синтаксис намного сложнее, потому что не создаются автоматические псевдостолбцы (как 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поддерживается EnterpriseDB , [20] базой данных Oracle , [21] CUBRID , [22] IBM Informix [23] и DB2, но только если он включен в качестве режима совместимости. [24] Синтаксис следующий:

ВЫБРАТЬ  список_выборов ИЗ  выражение_таблицы [  ГДЕ  ...  ] [  НАЧАТЬ  С  начального_выражения  ] ПОДКЛЮЧИТЬСЯ  ПО  [ NOCYCLE ]  {  PRIOR  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 также реализует запросы на фиксированные точки.
  • Дедуктивные базы данных
  • Иерархическая модель
  • Достижимость
  • Переходное закрытие
  • Древовидная структура

Ссылки [ править ]

  1. ^ a b Джим Мелтон; Алан Р. Саймон (2002). SQL: 1999: Понимание компонентов реляционного языка . Морган Кауфманн. ISBN 978-1-55860-456-8.
  2. ^ a b Microsoft. «Рекурсивные запросы с использованием общих табличных выражений» . Проверено 23 декабря 2009 .
  3. ^ Хелен Борри (2008-07-15). «Примечания к выпуску Firebird 2.1» . Проверено 24 ноября 2015 .
  4. ^ «С запросами» . PostgreSQL
  5. ^ "С пунктом" . SQLite
  6. ^ «MySQL 8.0 Labs: [Рекурсивные] общие табличные выражения в MySQL (CTE)» . mysqlserverteam.com
  7. ^ Paragon corporation: Использование пользовательских функций PostgreSQL для решения проблемы дерева , 15 февраля 2004 г., по состоянию на 19 сентября 2015 г.
  8. ^ Сравнение систем управления реляционными базами данных # Возможности баз данных
  9. ^ http://www.h2database.com/html/advanced.html#recursive_queries
  10. ↑ Карен Мортон; Робин Сэндс; Джаред Стилл; Риядж Шамсудин; Керри Осборн (2010). Профессиональный Oracle SQL . Апресс. п. 283. ISBN. 978-1-4302-3228-5.
  11. ↑ Карен Мортон; Робин Сэндс; Джаред Стилл; Риядж Шамсудин; Керри Осборн (2010). Профессиональный Oracle SQL . Апресс. п. 304. ISBN 978-1-4302-3228-5.
  12. ^ http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2z9.doc.apsg/src/tpc/db2z_xmprecursivecte.htm
  13. ^ http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=%2Fsqlp%2Frbafyrecursivequeries.htm
  14. ↑ Регина Обе; Лео Сюй (2012). PostgreSQL: готово и работает . O'Reilly Media. п. 94. ISBN 978-1-4493-2633-3.
  15. ^ Джим Мелтон; Алан Р. Саймон (2002). SQL: 1999: Понимание компонентов реляционного языка . Морган Кауфманн. п. 352. ISBN. 978-1-55860-456-8.
  16. ^ Дон Чемберлин (1998). Полное руководство по DB2 Universal Database . Морган Кауфманн. С. 253–254. ISBN 978-1-55860-482-7.
  17. ^ https://www.postgresql.org/docs/10/static/sql-createview.html
  18. ^ Бенедикт, М .; Сенелларт, П. (2011). «Базы данных». В Blum, Эдвард К .; Ахо, Альфред В. (ред.). Информатика. Аппаратное обеспечение, программное обеспечение и его суть . п. 189. DOI : 10.1007 / 978-1-4614-1168-0_10 . ISBN 978-1-4614-1167-3.
  19. ^ Санджай Мишра; Алан Больё (2004). Освоение Oracle SQL . O'Reilly Media, Inc. стр. 227. ISBN. 978-0-596-00632-7.
  20. ^ Иерархические запросы, заархивированные 21.06.2008 на Wayback Machine , EnterpriseDB
  21. ^ Иерархические запросы , Oracle
  22. ^ «Иерархический запрос CUBRID» . Проверено 11 февраля 2013 года .
  23. ^ Иерархический пункт , IBM Informix
  24. ^ Джонатан Генник (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