Рекурсивный запрос ms sql: Рекурсивные SQL запросы / Хабр

Рекурсивные SQL запросы / Хабр

Рекурсивны SQL запросы являются одним из способов решения проблемы дерева и других проблем, требующих рекурсивную обработку. Они были добавлены в стандарт SQL 99. До этого они уже существовали в Oracle. Несмотря на то, что стандарт вышел так давно, реализации запоздали. Например, в MS SQL они появились только в 2005-ом сервере.

Рекурсивные запросы используют довольно редко, прежде всего, из-за их сложного и непонятного синтаксиса:

with [recursive] <имя_алиаса_запроса> [ (<список столбцов>) ]
as (<запрос>)

<основной запрос>

В MS SQL нет ключевого слова recursive, а в остальном все тоже самое. Такой синтаксис поддерживается в DB2, Sybase iAnywhere, MS SQL и во всех базах данных, которые поддерживают стандарт SQL 99.

Проще разобрать на примере. Предположим, есть таблица:

create table tree_sample (

  id integer not null primary key,

  id_parent integer foreign key references tree_sample (id),

  nm varchar(31) )

id – идентификатор

id_parent – ссылка на родитель

nm – название.

Для вывода дерева:

with recursive tree (nm, id, level, pathstr)
as (select nm, id, 0, cast(» as text)

   from tree_sample

   where id_parent is null
union all

   select tree_sample.nm, tree_sample.id, t.level + 1, tree.pathstr + tree_sample.nm

   from tree_sample

     inner join tree on tree.id = tree_sample.id_parent)
select id, space( level ) + nm as nm
from tree
order by pathstr

Этот пример выведет дерево по таблице с отступами. Первый запрос из tree_sample этот запрос выдаст все корни дерева. Второй запрос соединяет между собой таблицу tree_sample и tree, которая определяется этим же запросом. Этот запрос дополняет таблицу узлами дерева.

Сначала выполняется первый запрос. Потом к его результатам добавляются результаты второго запроса, где данные таблица tree – это результат первого запроса. Затем снова выполняется второй запрос, но данные таблицы tree – это уже результат предыдущего выполнения второго запроса. И так далее. На самом деле база данных работает не совсем так, но результат будет таким же, как результат работы описанного алгоритма.

После этого данные этой таблицы можно использовать в основном запросе как обычно.

Хочу заметить, что я не говорю о применимости конкретно этого примера, а лишь пишу его для демонстрации возможностей рекурсивных запросов. Этот запрос реально будет работать достаточно медленно из-за order by.

Sql server — Рекурсивный запрос в MS SQL | GeekBrains

Нид хэлп!
Изучаю примеры рекурсивных запросов sql. Чтобы потренироваться с такими запросами, создал таблицу, в которой есть поля id (код записи) и id_parent (код родительской записи). Пример взял с одного сайта:

create table tree_sample (
  id integer not null primary key, 
  id_parent integer foreign key references tree_sample (id),nm varchar(31))

Таблицу наполнил данными. Для записей, у которых id_parent = NULL, считается, что это запись без родителя — самая верхняя в дереве (их может быть много). Для примера забил туда 2 строки: id = 1 «Книги», id = 2 «Фильмы». Затем забил еще несколько строк, в которых в id_parent = 1 или 2 (каждая с уникальным id и заголовком книги/фильма в поле nm).
После этого по тому же примеру из интернета слепил запрос:

with tree (nm, id, level, pathstr)
as (select nm, id, 0, cast(nm as varchar) 
   from tree_sample
   where id_parent is null 
union all
   select tree_sample.nm, tree_sample.id, tree.level + 5,
          cast(tree.nm + tree_sample.nm as varchar)
   from tree_sample 
     inner join tree on tree.id = tree_sample.id_parent) 
select id, space( level ) + nm as nm
from tree
order by pathstr

Пришлось допиливать напильником исходный запрос. Рекурсивный запрос sql, который дали в примере, сразу не работал. Но я допилил, и теперь все работает. Выводится табличка-дерево, в которой «Книги» и «Фильмы» без отступа слева, а под ними – заголовки книг/фильмов с отступом в 5 пробелов (space( level )).
Пока в таблице данные в 1 уровень вложенности (родитель и потомок), проблем не возникает, все отлично. Получается примерно так:

1 Книги
11 ----- Москва-Петушки
12 ----- Учебник по SQL
2 Фильмы
21 ----- Властелин колец
25 ----- Один дома

Ситуация меняется, если добавить потомков для одного из других потомков (например, к записи с фильмом «Властелин колец» добавить потомков: часть 1, 2, 3). Эти потомки выводятся с правильным отступом (уже в 10 пробелов), но не непосредственно под своим предком, а в случайном месте (ну, или не случайном, зависит от того, что попало в pathstr).
На этом я заглох. Не знаю, как слепить запрос так, чтобы ВСЕ потомки оказались непосредственно под своими предками.
Кто может подсказать, что нужно еще допилить в запросе?
Это вообще реально сделать?

Рекурсия в MS SQL — Fast Reports

10 января 2019 г.

Иногда, в хранимой процедуре или функции требуется использовать результаты выборки несколько раз. В таких случаях мы часто используем временные таблицы. Однако, стоит учитывать некоторые преимущества и недостатки временных таблиц. Преимущества:

• Временные таблицы являются полноценными таблицами. Поэтому для них можно создавать индексы и статистику. Это может существенно ускорить работу с ними.

Недостатки:

• Заполнение временной таблицы связано с перемещением данных. Хоть это и простая операция Insert, все же при больших объемах данных есть нагрузка на диски;
• Существует риск увеличения времени выполнения запросов. Временные таблицы создаются в базе tempdb. А нагрузка на эту базу существенная.

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

Обобщённое табличное выражение

Common Table Expression (CTE) выражение с общей таблицей, которую можно использовать множество раз в запросе. CTE не сохраняет данные, а создает нечто вроде временного представления.  Кто-то может сказать, что CTE – это подзапрос, который предшествует основному запросу. Но это не совсем так, ведь подзапрос нельзя использовать несколько раз, а CTE можно.

Когда же стоит использовать обобщенное табличное выражение?

1. Для создания рекурсивных запросов, с помощью которых можно получить данные в иерархическом виде;
2. При многократных ссылках на набор данных в пределах одного запроса;
3. С целью заменить представления, временные таблицы, табличные переменные.

К преимуществам CTE можно отнести: рекурсию, высокую скорость работы запроса, лаконичность запроса.

А к недостаткам отнесем ограниченность использования. CTE может использоваться только для запроса, к которому он принадлежит. Невозможно использовать его в других запросах. В этом случае придется использовать временные таблицы или табличные переменные.

Обобщенные табличные выражения бывают простые и рекурсивные.

Простые не включают ссылки на самого себя, а рекурсивные соответственно включают.

Рекурсивные CTE используются для возвращения иерархических данных

Рассмотрим пример простого CTE предложения:

1
2
3
4
5
6

 WITH CTEQuery (Field1, Field2)
 AS
 (
 SELECT (Field1, Field2) FROM TABLE
 )
 SELECT * FROM CTEQuery

 Здесь CTEQuery — имя CTE;

                Field1, Field2 – имена полей запроса;

                Table – некая таблица, из которой выбираются данные для использования в основном запросе.

В это примере можно и не указывать явно поля выборки, так как мы выбираем все поля из таблицы TestTable:

1
2
3
4
5
6

WITH CTEQuery
 AS
 (
 SELECT * FROM Table
 )
SELECT * FROM CTEQuery

С помощью CTE можно оптимизировать основной запрос если вынести часть логики в CTE. Дело в том, что CTE позволяет создавать сразу несколько выражений (запросов). Таким образом вы можете разбить сложный запрос на несколько предварительных «представлений» с помощью CTE, а затем связать их в общем запросе:

1
2
3
4
5
6
7
8
9
10
11
12

WITH CTEQuery1 (Field1, Field2) AS
(
 SELECT Field1 AS ID, Field2 FROM Table1
 WHERE Field2 >= 1000
),
CTEQuery2 (Field3, Field4) AS
(
 SELECT Field3 AS ID, Field4 FROM Table2
 WHERE Field4 = 'Москва'
)
 
SELECT * FROM CTEQuery1 INNER JOIN CTEQuery2 ON CTEQuery2.ID = CTEQuery1.ID

Как было сказано выше, основное назначение CTE – рекурсия. Типовая задача для рекурсии – обход дерева. Так что мы можем строить дерево с помощью with. Структура рекурсивного запроса впервые появилась в SQL Server 2005.

Взгляните на инструкцию WITH:

1
2
3
4
5
6
7

WITH RecursiveQuery AS
(
 {Anchor}
 UNION ALL
 {Joined TO RecursiveQuery}
)
SELECT * FROM RecursiveQuery

{Anchor} – якорь, запрос, который определяет начальный элемент дерева (иерархического списка). Обычно в якоре есть условие WHERE определяющее конкретные строки таблицы.

После UNION ALL следует запрос к целевой таблице с JOIN к CTE выражению.

{Joined to RecursiveQuery}- SELECT из целевой таблицы. Обычно это та же таблица, которая используется в якоре. Но в этом запросе она соединяется с CTE выражением, образуя рекурсию.  Условие этого соединения определяет отношение родитель – ребенок. От этого зависит переходите ли вы на верхние уровни дерева или на нижние.

Давайте посмотрим на рекурсивный запрос, который возвращает список подразделений организации. Подготовим данные для этого запроса:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

CREATE TABLE Department
(
ID INT,
ParentID INT,
Name VARCHAR(50)
)
 
INSERT INTO Department ( ID, ParentID, Name ) 
VALUES (1, 0, 'Finance Director')
INSERT INTO Department ( ID, ParentID, Name ) 
VALUES (2, 1, 'Deputy Finance Director')
INSERT INTO Department ( ID, ParentID, Name ) 
VALUES (3, 1, 'Assistance Finance Director')
INSERT INTO Department ( ID, ParentID, Name ) 
VALUES (4, 3, 'Executive Bodget Office')
INSERT INTO Department ( ID, ParentID, Name ) 
VALUES (5, 3, 'Comptroller')
INSERT INTO Department ( ID, ParentID, Name ) 
VALUES (6, 3, 'Purchasing')
INSERT INTO Department ( ID, ParentID, Name ) 
VALUES (7, 3, 'Debt Management')
INSERT INTO Department ( ID, ParentID, Name ) 
VALUES (8, 3, 'Risk Management')
INSERT INTO Department ( ID, ParentID, Name ) 
VALUES (9, 2, 'Public Relations')
INSERT INTO Department ( ID, ParentID, Name ) 
VALUES (10, 2, 'Finance Personnel')
INSERT INTO Department ( ID, ParentID, Name ) 
VALUES (11, 2, 'Finance Accounting')
INSERT INTO Department ( ID, ParentID, Name ) 
VALUES (12, 2, 'Liasion to Boards and Commissions')

Уже сейчас понятно, что структура подразделений в организации иерархическая. Наша задача получить список подразделений, подчиненных помощнику финансового директора. Если рассуждать в контексте иерархического дерева, то мы должны найти ветвь и ее листья.

Но сначала давайте посмотрим весь список подразделений:

Во главе стоит финансовый директор, ему подчиняются заместитель и помощник. Каждый из них имеет в своем ведении группу подразделений. Поле ParentID указывает на идентификатор «хозяина». Таким образом мы имеем уже готовую связь master-slave.

Давайте напишем рекурсивный запрос с помощью WITH.

1
2
3
4
5
6
7
8
9
10
11
12
13

WITH RecursiveQuery (ID, ParentID, Name)
AS
(
 SELECT ID, ParentID, Name
 FROM Department dep
 WHERE dep.ID = 3
 UNION ALL
 SELECT dep.ID, dep.ParentID, dep.Name
 FROM Department dep
 JOIN RecursiveQuery rec ON dep.ParentID = rec.ID
)
SELECT ID, ParentID, Name
FROM RecursiveQuery

В этом примере явно указаны названия полей, которые нужно выбрать в CTE. Однако, внутренние запросы имеют такие же поля. Так что можно просто убрать это перечисление вместе со скобками.

Внутри CTE мы имеем два похожих запроса. Первый выбирает корневой элемент дерева, которое мы строим. Второй – все последующие подчиненные элементы, благодаря связи с самим CTE. «Рекурсия» в SQL на самом деле не рекурсия, а итерация. Нужно представить запрос с JOIN как цикл, и тогда сразу все станет понятно. В каждой итерации мы знаем значение предыдущей выборки и получаем подчиненные элементы. На следующем шаге мы получим подчиненные элементы для предыдущей выборки. То есть каждая итерация – переход по дереву вниз, или вверх, в зависимости от условия связи.

Результат выполнения приведенного запроса такой:

А вот как бы выглядел этот запрос без использования CTE:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

DECLARE @Department TABLE (ID INT, ParentID INT, Name VARCHAR(50), Status INT DEFAULT 0)
-- Сначала мы выбираем в табличную переменную якорь – начальный элемент от которого строим дерево.
INSERT @Department
SELECT ID, ParentID, Name, 0
 FROM Department dep
 WHERE dep.ID = 3
 
DECLARE @rowsAdded INT = @@ROWCOUNT
-- Проходим цикл пока новые отделы добавляются в предыдущем шаге
WHILE @rowsAdded > 0 
BEGIN
-- Помечаем записи в табличной переменной, как готовые к обработке
UPDATE @Department SET Status = 1 WHERE Status = 0
-- Выбираем дочерние записи для предыдущей записи
INSERT @Department 
SELECT dep.ID, dep.ParentID, dep.Name, 0
 FROM Department dep
 JOIN @Department rec ON dep.ParentID = rec.ID
AND rec. Status = 1
SET @rowsAdded = @@ROWCOUNT 
 
 --Помечаем записи, найденные на текущем шаге как обработанные 
 UPDATE @Department SET Status = 2 WHERE Status = 1 
END
SELECT * FROM @Department

 Такой цикл работает заметно медленнее CTE выражения. Да и к тому же требует создания табличной переменной. И количество кода увеличилось в два раза. Таким образом, CTE выражения являются лучшим решением для организации рекурсивного обхода дерева в MS SQL.

Упражнения по SQL: Рекурсивные SQL запросы

Рекурсивны SQL запросы являются одним из способов решения проблемы дерева и других проблем, требующих рекурсивную обработку. Они были добавлены в стандарт SQL 99. До этого они уже существовали в Oracle. Несмотря на то, что стандарт вышел так давно, реализации запоздали. Например, в MS SQL они появились только в 2005-ом сервере.

Рекурсивные запросы используют довольно редко, прежде всего, из-за их сложного и непонятного синтаксиса:

with [recursive] <имя_алиаса_запроса> [ (<список столбцов>) ]
as (<запрос>)
<основной запрос>

В MS SQL нет ключевого слова recursive, а в остальном все тоже самое. Такой синтаксис поддерживается в DB2, Sybase iAnywhere, MS SQL и во всех базах данных, которые поддерживают стандарт SQL 99.

Проще разобрать на примере. Предположим, есть таблица:

create table tree_sample (
  id integer not null primary key,
  id_parent integer foreign key references tree_sample (id),
  nm varchar(31) )

id – идентификатор
id_parent – ссылка на родитель
nm – название.

Для вывода дерева:

with recursive tree (nm, id, level, pathstr)
as (select nm, id, 0, cast(» as text)
   from tree_sample
   where id_parent is null
union all
   select tree_sample.nm, tree_sample.id, t.level + 1, tree.pathstr + tree_sample.nm
   from tree_sample
     inner join tree on tree.id = tree_sample.id_parent)
select id, space( level ) + nm as nm
from tree
order by pathstr

Этот пример выведет дерево по таблице с отступами. Первый запрос из tree_sample этот запрос выдаст все корни дерева. Второй запрос соединяет между собой таблицу tree_sample и tree, которая определяется этим же запросом. Этот запрос дополняет таблицу узлами дерева.

Сначала выполняется первый запрос. Потом к его результатам добавляются результаты второго запроса, где данные таблица tree – это результат первого запроса. Затем снова выполняется второй запрос, но данные таблицы tree – это уже результат предыдущего выполнения второго запроса. И так далее. На самом деле база данных работает не совсем так, но результат будет таким же, как результат работы описанного алгоритма.

После этого данные этой таблицы можно использовать в основном запросе как обычно.

Хочу заметить, что я не говорю о применимости конкретно этого примера, а лишь пишу его для демонстрации возможностей рекурсивных запросов. Этот запрос реально будет работать достаточно медленно из-за order by.

Конструкция WITH в T-SQL или обобщенное табличное выражение (ОТВ) | Info-Comp.ru

Всем привет! Тема сегодняшнего материала будет посвящена обобщенным табличным выражениям языка T-SQL, мы с Вами узнаем, что это такое, а также рассмотрим примеры написания запросов с использованием этих самых обобщённых табличных выражений.

Для начала, конечно же, давайте поговорим о том, что вообще из себя представляют обобщенные табличные выражения, какие они бывают, рассмотрим синтаксис, для чего их можно использовать и в заключение разберем несколько примеров.

Заметка! Профессиональный видеокурс по T-SQL для начинающих.

Что такое обобщенное табличное выражение?

Common Table Expression (CTE) или обобщенное табличное выражение (OTB) – это временные результирующие наборы (т.е. результаты выполнения SQL запроса), которые не сохраняются в базе данных в виде объектов, но к ним можно обращаться.

Главной особенностью обобщенных табличных выражений является то, что с помощью них можно писать рекурсивные запросы, но об этом чуть ниже, а сейчас давайте поговорим о том, в каких случаях нам могут пригодиться OTB, в общем, для чего они предназначены:

• Основной целью OTB является написание рекурсивных запросов, можно сказать для этого они, и были созданы;
• OTB можно использовать также и для замены представлений (VIEW), например, в тех случаях, когда нет необходимости сохранять в базе SQL запрос представления, т.е. его определение;
• Обобщенные табличные выражения повышают читаемость кода путем разделения запроса на логические блоки, и тем самым упрощают работу со сложными запросами;
• Также OTB предназначены и для многократных ссылок на результирующий набор из одной и той же SQL инструкции.

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

Синтаксис:

 
   WITH  [ ( column_name [ ,...n ] ) ]
    AS
     ( CTE_query_definition )

Где,

• common_table_expression_name – это псевдоним или можно сказать идентификатор обобщенного табличного выражения. Обращаться к OTB мы будем, как раз используя этот псевдоним;
• column_name – имя столбца, который будет определен в обобщенном табличном выражении. Использование повторяющихся имен нельзя, а также их количество должно совпадать с количеством столбцов возвращаемых запросом CTE_query_definition. Указывать имена столбцов необязательно, но только в том случае, если всем столбцам в запросе CTE_query_definition присвоены уникальные псевдонимы;
• CTE_query_definition — запрос SELECT, к результирующему набору которого, мы и будем обращаться через обобщенное табличное выражение, т.е. common_table_expression_name.

После обобщенного табличного выражения, т.е. сразу за ним должен идти одиночный запрос SELECT, INSERT, UPDATE, MERGE или DELETE.

Какие бывают обобщенные табличные выражения?

Они бывают простые и рекурсивные.

Простые не включают ссылки на самого себя, а рекурсивные соответственно включают.

Рекурсивные ОТВ используются для возвращения иерархических данных, например, классика жанра это отображение сотрудников в структуре организации (чуть ниже мы это рассмотрим).

Примечание! Все примеры ниже будут рассмотрены в MS SQL Server 2008 R2.

В качестве тестовых данных давайте использовать таблицу TestTable, которая будет содержать идентификатор сотрудника, его должность и идентификатор его начальника.

Где,

• UserID — идентификатор сотрудника;
• Post — должность;
• ManagerID — идентификатор начальника.

Как видите, у директора отсутствует ManagerID, так как у него нет начальника. А теперь переходим к примерам.

Заметка! Обзор Azure Data Studio. Что это за инструмент и для чего он нужен.

Пример простого обобщенного табличного выражения

Для примера давайте просто выведем все содержимое таблицы TestTable с использованием обобщенного табличного выражения

 
   WITH TestCTE (UserID, Post, ManagerID)
        AS
        (
                SELECT UserID, Post, ManagerID FROM TestTable 
        )
   SELECT * FROM TestCTE

Где TestCTE это и есть псевдоним результирующего набора, к которому мы и обращаемся.

В данном случае мы могли и не перечислять имена столбцов, так как они у нас уникальны. Можно было просто написать вот так:

 
   WITH TestCTE
        AS
        (
                SELECT UserID, Post, ManagerID FROM TestTable 
        )
   SELECT * FROM TestCTE

Пример рекурсивного обобщенного табличного выражения

Теперь допустим, что нам необходимо вывести иерархический список сотрудников, т.е. мы хотим видеть, на каком уровне работает тот или иной сотрудник. Для этого пишем рекурсивный запрос:

 
   WITH TestCTE(UserID, Post, ManagerID, LevelUser)
   AS
   (    
        -- Находим якорь рекурсии
        SELECT UserID, Post, ManagerID, 0 AS LevelUser 
        FROM TestTable WHERE ManagerID IS NULL
        UNION ALL
        --Делаем объединение с TestCTE (хотя мы его еще не дописали)
        SELECT t1.UserID, t1.Post, t1.ManagerID, t2.LevelUser + 1 
        FROM TestTable t1 
        JOIN TestCTE t2 ON t1.ManagerID=t2.UserID
   )
   SELECT * FROM TestCTE ORDER BY LevelUser

В итоге, если мы захотим, мы можем легко получить список сотрудников определенного уровня, например, нам нужны только начальники отделов, для этого мы просто в указанный выше запрос добавим условие WHERE LevelUser = 1

При написании рекурсивного ОТВ нужно быть внимательным, так как неправильное его составление может привести к бесконечному циклу. Поэтому для этих целей есть опция MAXRECURSION, которая может ограничивать количество уровней рекурсии. Давайте представим, что мы не уверены, что написали рекурсивное обобщенное выражение правильно и для отладки напишем инструкцию OPTION (MAXRECURSION 5), т.е. отобразим только 5 уровня рекурсии, и если уровней будет больше, SQL инструкция будет прервана.

 
   WITH TestCTE(UserID, Post, ManagerID, LevelUser)
   AS
   (    
        --Находим якорь рекурсии
        SELECT UserID, Post, ManagerID, 0 AS LevelUser 
        FROM TestTable WHERE ManagerID IS NULL
        UNION ALL
        --Делаем объединение с TestCTE (хотя мы его еще не дописали)
        SELECT t1.UserID, t1.Post, t1.ManagerID, t2.LevelUser + 1 
        FROM TestTable t1 
        JOIN TestCTE t2 ON t1.ManagerID=t2.UserID
   )
   SELECT * FROM TestCTE ORDER BY LevelUser
   OPTION (MAXRECURSION 5)

Запрос у нас отработал, что говорит о том, что мы написали его правильно и соответственно OPTION (MAXRECURSION 5) можно смело убрать.

Заметка! Для комплексного изучения языка SQL и T-SQL рекомендую посмотреть мои видеокурсы по T-SQL, в которых используется последовательная методика обучения специально для начинающих.

На этом у меня все, удачи!

Нравится10Не нравится1

Полное руководство по рекурсивному CTE MySQL

Резюме : в этом руководстве вы узнаете о рекурсивном CTE MySQL и о том, как его использовать для просмотра иерархических данных.

Обратите внимание, что общее табличное выражение или CTE доступно только в MySQL версии 8.0 или новее. Следовательно, у вас должна быть установлена ​​правильная версия MySQL, чтобы использовать инструкции в этом руководстве.

Введение в рекурсивный CTE MySQL

Рекурсивное общее табличное выражение (CTE) — это CTE, который имеет подзапрос, который ссылается на само имя CTE.Ниже показан синтаксис рекурсивного CTE

 
  WITH RECURSIVE cte_name AS (
    initial_query - элемент привязки
    СОЮЗ ВСЕ
    recursive_query - рекурсивный член, который ссылается на имя CTE
)
ВЫБРАТЬ * ИЗ cte_name;
  
 

Рекурсивный CTE состоит из трех основных частей:

• Начальный запрос, который формирует базовый набор результатов структуры CTE. Начальная часть запроса называется якорным элементом.
• Рекурсивная часть запроса — это запрос, который ссылается на имя CTE, поэтому он называется рекурсивным членом.Рекурсивный элемент присоединяется к элементу привязки с помощью оператора UNION ALL или UNION DISTINCT .
• Условие завершения, которое гарантирует остановку рекурсии, когда рекурсивный член не возвращает строку.

Порядок выполнения рекурсивного CTE следующий:

1. Сначала разделите элементы на два: якорные и рекурсивные.
2. Затем выполните элемент привязки для формирования базового набора результатов ( R0 ) и используйте этот базовый набор результатов для следующей итерации.
3. Затем выполните рекурсивный элемент с набором результатов Ri в качестве входа и сделайте Ri + 1 в качестве выхода.
4. После этого повторяйте третий шаг, пока рекурсивный элемент не вернет пустой набор результатов, другими словами, условие завершения не будет выполнено.
5. Наконец, объедините наборы результатов от R0 до Rn с помощью оператора UNION ALL .

Ограничения рекурсивного элемента

Рекурсивный элемент не должен содержать следующие конструкции:

Обратите внимание, что указанное выше ограничение не применяется к элементу привязки.Также запрет на DISTINCT применяется только при использовании оператора UNION . В случае использования оператора UNION DISTINCT допускается использование оператора DISTINCT .

Кроме того, рекурсивный член может ссылаться на имя CTE только один раз и в своем предложении FROM , а не в каком-либо подзапросе.

Пример простого рекурсивного CTE MySQL

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

 
  С РЕКУРСИВНЫМ cte_count (n)
КАК (
      ВЫБРАТЬ 1
      СОЮЗ ВСЕ
      ВЫБРАТЬ n + 1
      ОТ cte_count
      ГДЕ n <3
    )
ВЫБЕРИТЕ n
ОТ cte_count;
  
 

В этом примере следующий запрос:

 
  SELECT 1
  
 

- элемент привязки, который возвращает 1 в качестве базового набора результатов.

Следующий запрос

 
  SELECT n + 1
ОТ cte_count
ГДЕ n <3
  
 

является рекурсивным членом, поскольку он ссылается на имя CTE, которое составляет cte_count .

Выражение n <3 в рекурсивном элементе является условием завершения. Когда n равно 3, рекурсивный член возвращает пустой набор, который останавливает рекурсию.

На следующем рисунке показаны элементы CTE выше:

Рекурсивный CTE возвращает следующий результат:

Шаги выполнения рекурсивного CTE следующие:

1. Сначала разделите элементы привязки и рекурсивные элементы.
2. Затем элемент привязки формирует начальную строку ( SELECT 1 ), поэтому первая итерация дает 1 + 1 = 2 с n = 1.
3. Затем вторая итерация работает с выходом первой итерации (2) и производит 2 + 1 = 3 с n = 2.
4. После этого, перед третьей операцией (n = 3), выполняется условие завершения ( n <3 ), поэтому запрос останавливается.
5. Наконец, объедините все наборы результатов 1, 2 и 3 с помощью оператора UNION ALL

Использование рекурсивного CTE MySQL для просмотра иерархических данных

Мы будем использовать таблицу сотрудников в базе данных примеров classicmodels для демонстрации .

Таблица сотрудников имеет столбец reportsTo , который ссылается на столбец employeeNumber . В столбце reportsTo хранятся идентификаторы менеджеров. Топ-менеджер не подчиняется никому в организационной структуре компании, поэтому значение в столбце reportsTo равно NULL .

Вы можете применить рекурсивный CTE для запроса всей организационной структуры сверху вниз следующим образом:

 
  С РЕКУРСИВНЫМИ путями-сотрудниками AS
  (ВЫБЕРИТЕ employeeNumber,
           reportsTo managerNumber,
           officeCode,
           1 уровень
   ОТ сотрудников
   WHERE reportsTo IS NULL
     СОЮЗ ВСЕ
     ВЫБРАТЬ e.Количество работников,
            e.reportsTo,
            e.officeCode,
            lvl + 1
     ОТ сотрудников e
     ВНУТРЕННЕЕ СОЕДИНЕНИЕ employee_paths ep ON ep.employeeNumber = e.reportsTo)
ВЫБЕРИТЕ employeeNumber,
       managerNumber,
       lvl,
       город
FROM employee_paths ep
ВНУТРЕННЕЕ СОЕДИНЕНИЕ офисов o ИСПОЛЬЗОВАНИЕ (officeCode)
ЗАКАЗАТЬ ПО ур., Городу;  
 

Давайте разберем запрос на более мелкие части, чтобы его было легче понять.

Сначала сформируйте элемент привязки, используя следующий запрос:

 
  SELECT
    Количество работников,
    reportsTo managerNumber,
    officeCode
ОТ
    сотрудники
ГДЕ
    reportsTo IS NULL
  
 

Этот запрос (элемент привязки) возвращает топ-менеджера, чей reportsTo равен NULL .

Во-вторых, создайте рекурсивный член по ссылке на CTE-имя, которое в данном случае составляет employee_paths :

 
  SELECT
    e.employeeNumber,
    e.reportsTo,
    e.officeCode
ОТ
    сотрудники е
ВНУТРЕННЕЕ ПРИСОЕДИНЕНИЕ EP
    НА ep.employeeNumber = e.reportsTo
  
 

Этот запрос (рекурсивный элемент) возвращает все прямые отчеты менеджера (ов) до тех пор, пока прямые отчеты не исчезнут. Если рекурсивный член не возвращает прямых отчетов, рекурсия останавливается.

В-третьих, запрос, в котором используется CTE employee_paths , объединяет набор результатов, возвращаемый CTE, с таблицей office для создания окончательного набора результатов.

Ниже приводится результат запроса:

В этом руководстве вы узнали о рекурсивном CTE MySQL и о том, как его использовать для просмотра иерархических данных.

• Было ли это руководство полезным?
• Да Нет

.

Рекурсивный запрос в MySQL с использованием хранимой процедуры и CURSOR

Переполнение стека

1. Около

2. Продукты

3. Для команд

1. Переполнение стека
   Общественные вопросы и ответы

2. Переполнение стека для команд
   Где разработчики и технологи делятся частными знаниями с коллегами

3. Вакансии
   Программирование и связанные с ним технические возможности карьерного роста

4. Талант
   Нанимайте технических специалистов и создавайте свой бренд работодателя

5. Реклама
   Обратитесь к разработчикам и технологам со всего мира

6. О компании

.

php - Как лучше всего обрабатывать большие рекурсивные запросы в mysql?

Переполнение стека

1. Около

2. Продукты

3. Для команд

1. Переполнение стека
   Общественные вопросы и ответы

2. Переполнение стека для команд
   Где разработчики и технологи делятся частными знаниями с коллегами

3. Вакансии
   Программирование и связанные с ним технические возможности карьерного роста

4. Талант
   Нанимайте технических специалистов и создавайте свой бренд работодателя

5. Реклама
   Обратитесь к разработчикам и технологам со всего мира

6. О компании

Загрузка…

.

рекурсивный запрос php mysql огромный в древовидной структуре

Переполнение стека

1. Около

2. Продукты

3. Для команд

1. Переполнение стека
   Общественные вопросы и ответы

2. Переполнение стека для команд
   Где разработчики и технологи делятся частными знаниями с коллегами

3. Вакансии
   Программирование и связанные с ним технические возможности карьерного роста

4. Талант
   Нанимайте технических специалистов и создавайте свой бренд работодателя

5. Реклама
   Обратитесь к разработчикам и технологам со всего мира

6. О компании

Загрузка…

.

No related posts.