Intereting Posts
Как преобразовать значение столбца в CamelCase с Oracle? В чем разница между хранимой процедурой и представлением? SQLite Query в алфавитном порядке, не чувствительном к регистру Запрос не возвращает результаты в MS Access с C #? Присоединитесь к двум таблицам, где таблица A имеет значение даты и должна найти следующую дату в B ниже даты в A COALESCE или CASE более эффективные и / или стандартные Преобразовать временную метку в читаемую дату во время запроса Многочисленные столбцы автоинкремента SQLite? Лучший способ выбора случайных строк PostgreSQL XML Oracle: Извлечение определенного атрибута из нескольких повторяющихся дочерних узлов Ошибка в объявлении переменной mysql bigint внутри пользовательской функции nextval Можно ли заказать несколько столбцов с помощью инструкции `ORDER BY FIELD`? как я могу получить долю десятичного числа в sql Как запросить нулевые значения в поле типа json postgresql? XML-путь Sql с разными детьми

Обнаружение цикла с рекурсивным подзапросом факторингом

Oracle SQL может выполнять иерархические запросы с v2, используя свой собственный синтаксис CONNECT BY. В своем последнем релизе 11g 2 они добавили рекурсивный факторинг подзапроса, также известный как рекурсивный с предложением. Это стандарт ANSI, и, если я правильно понимаю, этот был реализован другими поставщиками РСУБД.

Сравнивая соединение с рекурсивным с, я заметил разницу в наборе результатов при использовании определения цикла. Сопряжение по результатам более интуитивно для меня, поэтому мне интересно, если реализация Oracle содержит ошибку, или если это стандартное ANSI и ожидаемое поведение. Поэтому мой вопрос заключается в том, можно ли проверить рекурсивный запрос с помощью других баз данных, таких как MySQL, DB2, SQL Server и другие. При условии, что эти базы данных, конечно, поддерживают рекурсивную статью.

Вот как это работает на Oracle 11.2.0.1.0

SQL> select * 2 from t 3 / ID PARENT_ID ---------- ---------- 1 2 2 1 2 rows selected. 

Запрос с использованием синтаксиса CONNECT BY:

 SQL> select id 2 , parent_id 3 , connect_by_iscycle 4 from t 5 connect by nocycle parent_id = prior id 6 start with id = 1 7 / ID PARENT_ID CONNECT_BY_ISCYCLE ---------- ---------- ------------------ 1 2 0 2 1 1 2 rows selected. 

Это выглядит интуитивно понятным для меня. Однако, используя новый синтаксис ANSI, он возвращает еще одну строку:

 SQL> with tr (id,parent_id) as 2 ( select id 3 , parent_id 4 from t 5 where id = 1 6 union all 7 select t.id 8 , t.parent_id 9 from t 10 join tr on t.parent_id = tr.id 11 ) cycle id set is_cycle to '1' default '0' 12 select id 13 , parent_id 14 , is_cycle 15 from tr 16 / ID PARENT_ID I ---------- ---------- - 1 2 0 2 1 0 1 2 1 3 rows selected. 

Это скрипт, который вы можете использовать для проверки:

 create table t ( id number , parent_id number ); insert into t values (1, 2); insert into t values (2, 1); commit; with tr (id,parent_id) as ( select id , parent_id from t where id = 1 union all select t.id , t.parent_id from t join tr on t.parent_id = tr.id ) cycle id set is_cycle to '1' default '0' select id , parent_id , is_cycle from tr; 

Из документации по CONNECT_BY_ISCYCLE :

Псевдоконус CONNECT_BY_ISCYCLE возвращает 1 если в текущей строке есть дочерний элемент, который также является его предком

и что на CYCLE :

Считается, что строка образует цикл, если одна из его предковых рядов имеет одинаковые значения для столбцов цикла.

В вашем примере строка 2 имеет дочерний элемент, который также является его предком, но его id еще не возвращен.

Другими словами, CONNECT_BY_ISCYCLE проверяет дочерние элементы (которые еще не возвращены), а CYCLE проверяет текущую строку (которая уже возвращена).

CONNECT BY основан на строке, а рекурсивные CTE – на основе набора.

В рекурсивном CTE нет понятия «ребенок». Это операция, основанная на наборе, которая может полностью выводить результаты из дерева. Вообще говоря, якорная часть и рекурсивная часть могут даже использовать разные таблицы.

Поскольку рекурсивные CTE обычно используются для построения деревьев иерархии, Oracle решила добавить проверку цикла. Но из-за того, что рекурсивный CTE работает на основе набора, обычно невозможно сказать, будет ли следующий шаг генерировать цикл или нет.

Для выполнения «следующего» шага весь «текущий» набор должен быть доступен, но для генерации каждой строки текущего набора (который включает столбец цикла) нам просто нужно иметь результаты «следующей» операции. Это не проблема с одной строкой (например, в CONNECT BY ), но это проблема с набором в целом.

Еще не изучал Oracle 11 , но SQL Server реализует рекурсивные CTE , просто скрывая за ними CONNECT BY , что требует размещения многочисленных ограничений (все из которых эффективно запрещают все операции на основе набора).

PostgreSQL другой стороны, реализация PostgreSQL действительно основана на настройках.

Как уже упоминалось ранее, MySQL вообще не реализует CTE (он не реализует HASH JOIN или MERGE JOIN s, а только вложенные циклы, поэтому не удивляйтесь).

Как ни странно, сегодня я получил письмо по этому вопросу, которое я расскажу в своем блоге.

Обновить:

Рекурсивные CTE в SQL Server – это не более чем CONNECT BY . Смотрите эту статью в своем блоге для шокирующих деталей:

  • SQL Server: рекурсивные CTE действительно настроены?

PostgreSQL поддерживает иерархические запросы в стиле WITH, но не имеет автоматического обнаружения цикла. Это означает, что вам нужно написать свой собственный, и количество возвращаемых строк зависит от того, как вы указываете условия соединения в рекурсивной части запроса.

Оба примера используют массив, если идентификаторы (называемые all_ids) для обнаружения циклов:

 WITH recursive tr (id, parent_id, all_ids, cycle) AS ( SELECT id, parent_id, ARRAY[id], false FROM t WHERE id = 1 UNION ALL SELECT t.id, t.parent_id, all_ids || t.id, t.id = ANY(all_ids) FROM t JOIN tr ON t.parent_id = tr.id AND NOT cycle) SELECT id, parent_id, cycle FROM tr; id | parent_id | cycle ----+-----------+------- 1 | 2 | f 2 | 1 | f 1 | 2 | t WITH recursive tr (id, parent_id, all_ids, cycle) AS ( SELECT id, parent_id, ARRAY[id], false FROM t WHERE id = 1 UNION ALL SELECT t.id, t.parent_id, all_ids || t.id, (EXISTS(SELECT 1 FROM t AS x WHERE x.id = t.parent_id)) FROM t JOIN tr ON t.parent_id = tr.id WHERE NOT t.id = ANY(all_ids)) SELECT id, parent_id, cycle FROM tr; id | parent_id | cycle ----+-----------+------- 1 | 2 | f 2 | 1 | t 

НАСКОЛЬКО МНЕ ИЗВЕСТНО:

  • MySQL не поддерживает рекурсивный CTE
  • SQL Sever не поддерживает обнаружение цикла в рекурсивном CTE

MySQL Server версии 5.0.45 не понравилось:

ERROR 1064 (42000): у вас есть ошибка в синтаксисе SQL; проверьте руководство, соответствующее версии вашего сервера MySQL, для правильного синтаксиса для использования рядом с 'с tr (id, parent_id) как (выберите id, parent_id из t, где id = 1 union all s' в строке 1.

 WITH RECURSIVE s (master, slave, all_ids, cycle) AS ( SELECT master, slave, ARRAY[master], false FROM binding WHERE master=3477 UNION ALL SELECT d.master, d.slave, all_ids || d.master, d.slave = ANY(all_ids) FROM binding AS d JOIN s ON (d.master = s.slave) WHERE NOT d.master = ANY(all_ids) ) SELECT * FROM s; 

Я думаю, что лучше это условие d.slave = ANY(all_ids)