Azərbaycanca AzərbaycancaБеларускі БеларускіDansk DanskDeutsch DeutschEspañola EspañolaFrançais FrançaisIndonesia IndonesiaItaliana Italiana日本語 日本語Қазақ ҚазақLietuvos LietuvosNederlands NederlandsPortuguês PortuguêsРусский Русскийසිංහල සිංහලแบบไทย แบบไทยTürkçe TürkçeУкраїнська Українська中國人 中國人United State United StateAfrikaans Afrikaans
Підтримка
www.wikidata.uk-ua.nina.az

Цю статтю написано занадто професійним стилем зі специфічною термінологією що може бути незрозумілим для більшості читач

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

Ієрархічні та рекурсивні запити в SQL
Цю статтю написано занадто професійним зі специфічною термінологією, що може бути незрозумілим для більшості читачів. Ви можете допомогти вдосконалити цю статтю, зробивши її зрозумілою для неспеціалістів без втрат змісту. Можливо, містить зауваження щодо потрібних змін. (квітень 2018)

Ієрархічний запит — тип запиту SQL, що обробляє ієрархічну модель даних. Вони є особливим випадком загальніших рекурсивних нерухомих запитів, які обчислюють транзитивні замикання.

У стандарті SQL:1999 ієрархічні запити реалізовані шляхом рекурсивних загальних табличних виразів (ЗТВ). На відміну від більш ранніх умов connect-by в Oracle, рекурсивні ЗТВ було розроблено з нерухомою семантикою від початку. Рекурсивні ЗТВ зі стандарту були відносно близькі до наявної реалізації в IBM DB2 версії 2. Рекурсивні ЗТВ також підтримуються Microsoft SQL Server (починаючи з SQL Server 2008 R2), Firebird 2.1, PostgreSQL 8.4+, SQLite 3.8.3+, IBM Informix версії 11.50+, [en], MariaDB 10.2+ і MySQL 8.0.1+. Tableau має документацію, що описує, як ЗТВ можуть використовуватися. TIBCO Spotfire не підтримує ЗТВ, тоді як реалізації Oracle 11g Release 2 бракує нерухомої семантики.

Без загальних табличних виразів або умов connected-by можливо досягти ієрархічних запитів за допомогою користувацьких рекурсивних функцій.

Загальний табличний вираз

Загальний табличний вираз, або ЗТВ (в SQL) — тимчасовий іменований результатний набір, що походить із простого запиту та визначений усередині області виконання інструкції SELECT, INSERT, UPDATE чи DELETE.

ЗТВ можна вважати альтернативами похідним таблицям (підзапитам), розрізам і вбудованим користувацьким функціям.

Загальні табличні вирази підтримуються Teradata, DB2, Firebird, Microsoft SQL Server, Oracle (з рекурсією, починаючи з 11g release 2), PostgreSQL (починаючи з 8.4), MariaDB (починаючи з 10.2), MySQL (починаючи з 8.0), SQLite (починаючи з 3.8.3), HyperSQL і [en] (експериментально). Oracle називає ЗТВ «підзапитним факторингом» (англ. subquery factoring).

Синтаксис для рекурсивного ЗТВ виглядає наступним чином: 

WITH [RECURSIVE] запит_with [, …] SELECT …

де синтаксисом запит_with є: 

назва_запиту [ (назва_колонки [, …]) ] AS (SELECT …)

Рекурсивні ЗТВ (або «рекурсивний підзапитний факторинг» у жаргоні Oracle) можуть використовуватися для обходу відношень (як графів або дерев), хоча синтаксис набагато більше залучений через відсутність створених автоматичних псевдо-колонок (як LEVEL нижче); якщо вони є бажаними, то їх слід створити в коді. Навчальні приклади див. у документації MSDN або IBM.

Ключове слово RECURSIVE зазвичай не є необхідним після WITH у системах, крім PostgreSQL.

В SQL:1999 рекурсивний (ЗТВ) запит може з'являтися будь-де, де дозволено запит. Можливо, наприклад, назвати результат за допомогою CREATE [RECURSIVE] VIEW. За допомогою ЗТВ усередині INSERT INTO можна наповнити таблицю даними, згенерованими з рекурсивного запиту; генерація випадкових даних можлива з використанням цієї техніки без використання жодних процедурних інструкцій.

Деякі бази даних на кшталт PostgreSQL підтримують скорочений формат CREATE RECURSIVE VIEW, який внутрішньо перекладається в кодування WITH RECURSIVE.

Прикладом рекурсивного запиту, що обчислює факторіал чисел від 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

Альтернативним синтаксисом є нестандартна конструкція CONNECT BY; її було впроваджено Oracle у 1980-х. До Oracle 10g конструкція була корисною тільки для обходу ациклічних графів, оскільки вона повертала помилку при виявленні будь-яких циклів; у версії 10g Oracle впровадила можливість NOCYCLE (та ключове слово), уможливлюючи роботу з обходу і за наявності циклів.

CONNECT BY підтримується EnterpriseDB, Oracle Database, [en], IBM Informix і DB2, хоча тільки, якщо його увімкнено як режим сумісності. Синтаксис виглядає наступним чином: 

SELECT список_вибірки FROM табличний_вираз [ WHERE … ] [ START WITH початковий_вираз ] CONNECT BY [NOCYCLE] { PRIOR дочірній_вираз = батьківський_вираз | батьківський_вираз = PRIOR дочірній_вираз } [ ORDER SIBLINGS BY колонка1 [ ASC | DESC ] [, колонка2 [ ASC | DESC ] ] … [ GROUP BY … ] [ HAVING … ] …
Наприклад,

Виведення з вищенаведеного запиту виглядатиме як: 

 level | працівник | empno | менеджер -------+-------------+-------+---------- 1 | KING | 7839 | 2 | JONES | 7566 | 7839 3 | SCOTT | 7788 | 7566 4 | ADAMS | 7876 | 7788 3 | FORD | 7902 | 7566 4 | SMITH | 7369 | 7902 2 | BLAKE | 7698 | 7839 3 | ALLEN | 7499 | 7698 3 | WARD | 7521 | 7698 3 | MARTIN | 7654 | 7698 3 | TURNER | 7844 | 7698 3 | JAMES | 7900 | 7698 2 | CLARK | 7782 | 7839 3 | MILLER | 7934 | 7782 (14 рядків)

Псевдо-колонки

  • LEVEL
  • CONNECT_BY_ISLEAF
  • CONNECT_BY_ISCYCLE
  • CONNECT_BY_ROOT

Унарні оператори

Наступний приклад повертає прізвище кожного працівника у відділі 10, кожного менеджера над цим працівником в ієрархії, кількість рівнів між менеджером і працівником і шлях між ними: 

SELECT ename "Працівник", CONNECT_BY_ROOT ename "Менеджер",  LEVEL - 1 "Довжина шляху", SYS_CONNECT_BY_PATH(ename, '/') "Шлях" FROM emp WHERE LEVEL > 1 and deptno = 10 CONNECT BY PRIOR empno = mgr ORDER BY "Працівник", "Менеджер", "Довжина шляху", "Шлях";

Функції

  • SYS_CONNECT_BY_PATH

Див. також

Примітки

  1. Melton, Jim; Simon, Alan R. (2002). . Morgan Kaufmann. с. 352. ISBN . Архів оригіналу за 29 липня 2020. Процитовано 9 серпня 2019.
  2. Microsoft. . MSDN. Архів оригіналу за 8 грудня 2009. Процитовано 23 грудня 2009.
  3. Borrie, Helen (15 липня 2008). . Архів оригіналу за 22 квітня 2017. Процитовано 24 листопада 2015.
  4. . PostgreSQL. Архів оригіналу за 1 травня 2016. Процитовано 9 серпня 2019.
  5. . SQLite. Архів оригіналу за 5 липня 2019. Процитовано 9 серпня 2019.
  6. . mysqlserverteam.com. Архів оригіналу за 16 серпня 2019. Процитовано 9 серпня 2019.
  7. . kb.tableau.com (англійською) . 8 січня 2019 [2016]. Архів оригіналу за 1 серпня 2019. Процитовано 9 серпня 2019.
  8. . 15 лютого 2004. Архів оригіналу за 23 вересня 2015. Процитовано 19 вересня 2015.
  9. [en]
  10. . h2database.com (англійською) . Архів оригіналу за 9 липня 2006. Процитовано 9 серпня 2019.
  11. Morton, Karen; Sands, Robyn; Still, Jared; Shamsudeen, Riyaj; Osborne, Kerry (2010). . Apress. с. 283. ISBN . Архів оригіналу за 29 липня 2020. Процитовано 9 серпня 2019.
  12. Morton, Karen; Sands, Robyn; Still, Jared; Shamsudeen, Riyaj; Osborne, Kerry (2010). . Apress. с. 304. ISBN . Архів оригіналу за 29 липня 2020. Процитовано 9 серпня 2019.
  13. http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2z9.doc.apsg/src/tpc/db2z_xmprecursivecte.htm
  14. http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=%2Fsqlp%2Frbafyrecursivequeries.htm
  15. Obe, Regina; Hsu, Leo (2012). . O'Reilly Media. с. 94. ISBN . Архів оригіналу за 29 липня 2020. Процитовано 9 серпня 2019.
  16. Chamberlin, Don (1998). . Morgan Kaufmann. с. 253—254. ISBN . Архів оригіналу за 29 липня 2020. Процитовано 9 серпня 2019.
  17. . Архів оригіналу за 4 жовтня 2018. Процитовано 9 серпня 2019.{{}}: Обслуговування CS1: Сторінки з текстом «archived copy» як значення параметру title ()
  18. Benedikt, M.; Senellart, P. (2011). Databases. У Blum, Edward K.; Aho, Alfred V. (ред.). Computer Science. The Hardware, Software and Heart of It. с. 189. doi:10.1007/978-1-4614-1168-0_10. ISBN .
  19. Beaulieu, Alan (2004). . O'Reilly Media, Inc. с. 227. ISBN . Архів оригіналу за 29 липня 2020. Процитовано 9 серпня 2019. {{}}: |ім'я1= з пропущеним |ім'я1= ()
  20. . EnterpriseDB. Архів оригіналу за 21 червня 2008. Процитовано 9 серпня 2019.
  21. . Oracle. Архів оригіналу за 8 листопада 2011. Процитовано 9 серпня 2019.
  22. . Архів оригіналу за 14 лютого 2013. Процитовано 11 лютого 2013.
  23. . IBM Informix. Архів оригіналу за 4 березня 2016. Процитовано 9 серпня 2019.
  24. Gennick, Jonathan (2010). (вид. 3-є). O'Reilly Media, Inc. с. 8. ISBN . Архів оригіналу за 29 липня 2020. Процитовано 9 серпня 2019.

Література

  • Date, C. J. (2011). SQL and Relational Theory: How to Write Accurate SQL Code (англійською) (вид. 2-е). O'Reilly Media. с. 159—163. ISBN .

Академічні підручники. Зверніть увагу, що вони покривають тільки SQL:1999 (та Datalog), але не розширення Oracle.

  • Silberschatz, Abraham; Korth, Henry; Sudarshan, S. (2010). (англійською) (вид. 6-е). McGraw-Hill. с. 187—192. ISBN .
  • Ramakrishnan, Raghu; Gehrke, Johannes (2003). Chapter 24. Database management systems (англійською) (вид. 3-є). McGraw-Hill. ISBN .
  • ; Ullman, Jeffrey D.; Widom, Jennifer (2009). Database systems: the complete book (англійською) (вид. 2-е). Pearson Prentice Hall. с. 437—445. ISBN .

Посилання

Вікіпедія, Українська, Україна, книга, книги, бібліотека, стаття, читати, завантажити, безкоштовно, безкоштовно завантажити, mp3, відео, mp4, 3gp, jpg, jpeg, gif, png, малюнок, музика, пісня, фільм, книга, гра, ігри, мобільний, телефон, android, ios, apple, мобільний телефон, samsung, iphone, xiomi, xiaomi, redmi, honor, oppo, nokia, sonya, mi, ПК, web, Інтернет

Cyu stattyu napisano zanadto profesijnim stilem zi specifichnoyu terminologiyeyu sho mozhe buti nezrozumilim dlya bilshosti chitachiv Vi mozhete dopomogti vdoskonaliti cyu stattyu zrobivshi yiyi zrozumiloyu dlya nespecialistiv bez vtrat zmistu Mozhlivo mistit zauvazhennya shodo potribnih zmin kviten 2018 Iyerarhichnij zapit tip zapitu SQL sho obroblyaye iyerarhichnu model danih Voni ye osoblivim vipadkom zagalnishih rekursivnih neruhomih zapitiv yaki obchislyuyut tranzitivni zamikannya U standarti SQL 1999 iyerarhichni zapiti realizovani shlyahom rekursivnih zagalnih tablichnih viraziv ZTV Na vidminu vid bilsh rannih umov connect by v Oracle rekursivni ZTV bulo rozrobleno z neruhomoyu semantikoyu vid pochatku Rekursivni ZTV zi standartu buli vidnosno blizki do nayavnoyi realizaciyi v IBM DB2 versiyi 2 Rekursivni ZTV takozh pidtrimuyutsya Microsoft SQL Server pochinayuchi z SQL Server 2008 R2 Firebird 2 1 PostgreSQL 8 4 SQLite 3 8 3 IBM Informix versiyi 11 50 en MariaDB 10 2 i MySQL 8 0 1 Tableau maye dokumentaciyu sho opisuye yak ZTV mozhut vikoristovuvatisya TIBCO Spotfire ne pidtrimuye ZTV todi yak realizaciyi Oracle 11g Release 2 brakuye neruhomoyi semantiki Bez zagalnih tablichnih viraziv abo umov connected by mozhlivo dosyagti iyerarhichnih zapitiv za dopomogoyu koristuvackih rekursivnih funkcij Zagalnij tablichnij virazZagalnij tablichnij viraz abo ZTV v SQL timchasovij imenovanij rezultatnij nabir sho pohodit iz prostogo zapitu ta viznachenij useredini oblasti vikonannya instrukciyi SELECT INSERT UPDATE chi DELETE ZTV mozhna vvazhati alternativami pohidnim tablicyam pidzapitam rozrizam i vbudovanim koristuvackim funkciyam Zagalni tablichni virazi pidtrimuyutsya Teradata DB2 Firebird Microsoft SQL Server Oracle z rekursiyeyu pochinayuchi z 11g release 2 PostgreSQL pochinayuchi z 8 4 MariaDB pochinayuchi z 10 2 MySQL pochinayuchi z 8 0 SQLite pochinayuchi z 3 8 3 HyperSQL i en eksperimentalno Oracle nazivaye ZTV pidzapitnim faktoringom angl subquery factoring Sintaksis dlya rekursivnogo ZTV viglyadaye nastupnim chinom WITH RECURSIVE zapit with SELECT de sintaksisom zapit with ye nazva zapitu nazva kolonki AS SELECT Rekursivni ZTV abo rekursivnij pidzapitnij faktoring u zhargoni Oracle mozhut vikoristovuvatisya dlya obhodu vidnoshen yak grafiv abo derev hocha sintaksis nabagato bilshe zaluchenij cherez vidsutnist stvorenih avtomatichnih psevdo kolonok yak LEVEL nizhche yaksho voni ye bazhanimi to yih slid stvoriti v kodi Navchalni prikladi div u dokumentaciyi MSDN abo IBM Klyuchove slovo RECURSIVE zazvichaj ne ye neobhidnim pislya WITH u sistemah krim PostgreSQL V SQL 1999 rekursivnij ZTV zapit mozhe z yavlyatisya bud de de dozvoleno zapit Mozhlivo napriklad nazvati rezultat za dopomogoyu span class k CREATE span span class w span span class p span span class k RECURSIVE span span class p span span class w span span class k VIEW span Za dopomogoyu ZTV useredini INSERT INTO mozhna napovniti tablicyu danimi zgenerovanimi z rekursivnogo zapitu generaciya vipadkovih danih mozhliva z vikoristannyam ciyeyi tehniki bez vikoristannya zhodnih procedurnih instrukcij Deyaki bazi danih na kshtalt PostgreSQL pidtrimuyut skorochenij format CREATE RECURSIVE VIEW yakij vnutrishno perekladayetsya v koduvannya WITH RECURSIVE Prikladom rekursivnogo zapitu sho obchislyuye faktorial chisel vid 0 do 9 ye nastupne WITH RECURSIVE temp n fact AS SELECT 0 1 Pochatkovij pidzapit UNION ALL SELECT n 1 n 1 fact Rekursivnij pidzapit FROM temp WHERE n lt 9 SELECT FROM temp CONNECT BYAlternativnim sintaksisom ye nestandartna konstrukciya CONNECT BY yiyi bulo vprovadzheno Oracle u 1980 h Do Oracle 10g konstrukciya bula korisnoyu tilki dlya obhodu aciklichnih grafiv oskilki vona povertala pomilku pri viyavlenni bud yakih cikliv u versiyi 10g Oracle vprovadila mozhlivist NOCYCLE ta klyuchove slovo umozhlivlyuyuchi robotu z obhodu i za nayavnosti cikliv CONNECT BY pidtrimuyetsya EnterpriseDB Oracle Database en IBM Informix i DB2 hocha tilki yaksho jogo uvimkneno yak rezhim sumisnosti Sintaksis viglyadaye nastupnim chinom SELECT spisok vibirki FROM tablichnij viraz WHERE START WITH pochatkovij viraz CONNECT BY NOCYCLE PRIOR dochirnij viraz batkivskij viraz batkivskij viraz PRIOR dochirnij viraz ORDER SIBLINGS BY kolonka 1 ASC DESC kolonka 2 ASC DESC GROUP BY HAVING Napriklad Vivedennya z vishenavedenogo zapitu viglyadatime yak level pracivnik empno menedzher 1 KING 7839 2 JONES 7566 7839 3 SCOTT 7788 7566 4 ADAMS 7876 7788 3 FORD 7902 7566 4 SMITH 7369 7902 2 BLAKE 7698 7839 3 ALLEN 7499 7698 3 WARD 7521 7698 3 MARTIN 7654 7698 3 TURNER 7844 7698 3 JAMES 7900 7698 2 CLARK 7782 7839 3 MILLER 7934 7782 14 ryadkiv Psevdo kolonki LEVEL CONNECT BY ISLEAF CONNECT BY ISCYCLE CONNECT BY ROOTUnarni operatori Nastupnij priklad povertaye prizvishe kozhnogo pracivnika u viddili 10 kozhnogo menedzhera nad cim pracivnikom v iyerarhiyi kilkist rivniv mizh menedzherom i pracivnikom i shlyah mizh nimi SELECT ename Pracivnik CONNECT BY ROOT ename Menedzher LEVEL 1 Dovzhina shlyahu SYS CONNECT BY PATH ename Shlyah FROM emp WHERE LEVEL gt 1 and deptno 10 CONNECT BY PRIOR empno mgr ORDER BY Pracivnik Menedzher Dovzhina shlyahu Shlyah Funkciyi SYS CONNECT BY PATHDiv takozhDatalog takozh realizuye neruhomi zapiti Deduktivni bazi danih Derevopodibna struktura en Iyerarhichna model danih Tranzitivne zamikannyaPrimitkiMelton Jim Simon Alan R 2002 Morgan Kaufmann s 352 ISBN 978 1 55860 456 8 Arhiv originalu za 29 lipnya 2020 Procitovano 9 serpnya 2019 Microsoft MSDN Arhiv originalu za 8 grudnya 2009 Procitovano 23 grudnya 2009 Borrie Helen 15 lipnya 2008 Arhiv originalu za 22 kvitnya 2017 Procitovano 24 listopada 2015 PostgreSQL Arhiv originalu za 1 travnya 2016 Procitovano 9 serpnya 2019 SQLite Arhiv originalu za 5 lipnya 2019 Procitovano 9 serpnya 2019 mysqlserverteam com Arhiv originalu za 16 serpnya 2019 Procitovano 9 serpnya 2019 kb tableau com anglijskoyu 8 sichnya 2019 2016 Arhiv originalu za 1 serpnya 2019 Procitovano 9 serpnya 2019 15 lyutogo 2004 Arhiv originalu za 23 veresnya 2015 Procitovano 19 veresnya 2015 en h2database com anglijskoyu Arhiv originalu za 9 lipnya 2006 Procitovano 9 serpnya 2019 Morton Karen Sands Robyn Still Jared Shamsudeen Riyaj Osborne Kerry 2010 Apress s 283 ISBN 978 1 4302 3228 5 Arhiv originalu za 29 lipnya 2020 Procitovano 9 serpnya 2019 Morton Karen Sands Robyn Still Jared Shamsudeen Riyaj Osborne Kerry 2010 Apress s 304 ISBN 978 1 4302 3228 5 Arhiv originalu za 29 lipnya 2020 Procitovano 9 serpnya 2019 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 Obe Regina Hsu Leo 2012 O Reilly Media s 94 ISBN 978 1 4493 2633 3 Arhiv originalu za 29 lipnya 2020 Procitovano 9 serpnya 2019 Chamberlin Don 1998 Morgan Kaufmann s 253 254 ISBN 978 1 55860 482 7 Arhiv originalu za 29 lipnya 2020 Procitovano 9 serpnya 2019 Arhiv originalu za 4 zhovtnya 2018 Procitovano 9 serpnya 2019 a href wiki D0 A8 D0 B0 D0 B1 D0 BB D0 BE D0 BD Cite web title Shablon Cite web cite web a Obslugovuvannya CS1 Storinki z tekstom archived copy yak znachennya parametru title posilannya Benedikt M Senellart P 2011 Databases U Blum Edward K Aho Alfred V red Computer Science The Hardware Software and Heart of It s 189 doi 10 1007 978 1 4614 1168 0 10 ISBN 978 1 4614 1167 3 Beaulieu Alan 2004 O Reilly Media Inc s 227 ISBN 978 0 596 00632 7 Arhiv originalu za 29 lipnya 2020 Procitovano 9 serpnya 2019 a href wiki D0 A8 D0 B0 D0 B1 D0 BB D0 BE D0 BD Cite book title Shablon Cite book cite book a im ya1 z propushenim im ya1 dovidka EnterpriseDB Arhiv originalu za 21 chervnya 2008 Procitovano 9 serpnya 2019 Oracle Arhiv originalu za 8 listopada 2011 Procitovano 9 serpnya 2019 Arhiv originalu za 14 lyutogo 2013 Procitovano 11 lyutogo 2013 IBM Informix Arhiv originalu za 4 bereznya 2016 Procitovano 9 serpnya 2019 Gennick Jonathan 2010 vid 3 ye O Reilly Media Inc s 8 ISBN 978 1 4493 9409 7 Arhiv originalu za 29 lipnya 2020 Procitovano 9 serpnya 2019 LiteraturaDate C J 2011 SQL and Relational Theory How to Write Accurate SQL Code anglijskoyu vid 2 e O Reilly Media s 159 163 ISBN 978 1 4493 1640 2 Akademichni pidruchniki Zvernit uvagu sho voni pokrivayut tilki SQL 1999 ta Datalog ale ne rozshirennya Oracle Silberschatz Abraham Korth Henry Sudarshan S 2010 anglijskoyu vid 6 e McGraw Hill s 187 192 ISBN 978 0 07 352332 3 Ramakrishnan Raghu Gehrke Johannes 2003 Chapter 24 Database management systems anglijskoyu vid 3 ye McGraw Hill ISBN 978 0 07 246563 1 Ullman Jeffrey D Widom Jennifer 2009 Database systems the complete book anglijskoyu vid 2 e Pearson Prentice Hall s 437 445 ISBN 978 0 13 187325 4 Posilannyahttps stackoverflow com questions 1731889 cycle detection with recursive subquery factoring 14 kvitnya 2020 u Wayback Machine http explainextended com 2009 11 18 sql server are the recursive ctes really set based 1 serpnya 2020 u Wayback Machine http www cs duke edu courses fall04 cps116 lectures 11 recursion pdf 1 serpnya 2020 u Wayback Machine http www blacktdn com br 2015 06 blacktdn mssql usando consulta cte html 28 lipnya 2020 u Wayback Machine

Дата публікації:
Топ