ГлавнаяРазноеMs sql рекурсивный запрос

Вызов рекурсивного запроса T-SQL. Ms sql рекурсивный запрос


рекурсия - ms sql задача по рекурсии

Поскольку опыта нет, рассмотрим рекурсивные выражения и затем применим их на практике.

Рекурсия имеет свойство хранить в себе память любых предыдущих значений. Зависит от реализации. Как правило это предыдущие значения. В нашем примере также будем использовать корневое значение. Какое значение? Интересует зарплата.

Рекурсия в SQL CTE

Рекурсивное выражение состоит из двух частей: корневая (ancor member expression) и рекурсивная (recursive member expression)

В корневой части составляется обыкновенный запрос на выборку и задается общая структура полей в выражении:

SELECT e.Salary , e.ID , e.ManagerID , 0 AS Stack FROM employees e

В рекурсивной части - запрос который будет добавляться к предыдущей (пока что корневой) записи с помощью оператора UNION :

UNION ALL -- recursive member expression SELECT e.Salary , e.ID , e.ManagerID , c.Stack + 1 AS Stack FROM employees e JOIN __cte c ON c.Id = e.ManagerID

Если собрать из этих двух частей выражение:

; WITH __cte AS ( -- ancor member expression SELECT e.Salary , e.ID , e.ManagerID , 0 AS Stack FROM employees e UNION ALL -- recursive member expression SELECT e.Salary , e.ID , e.ManagerID , c.Stack + 1 AS Stack FROM employees e JOIN __cte c ON c.Id = e.ManagerID ) SELECT c.ID, c.Salary, c.Stack from __cte c order by id, Stack

и запустить его - будет получена выборка по всему персоналу с обходом их подчиненных:

Как можно заметить, здесь уже участвует "предыдущее" значение Stack.

Применение рекурсии

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

В корневую часть запроса добавил проекцию на зарплату, разницу и айди с которым идет сравнение (для трассировки):

-- ancor member expression SELECT e.Salary , e.ID , e.ManagerID -- порядок полей очень важен , e.Salary AS RootSalary , 0 AS RootSalaryDiff , e.ID AS RootID , 0 AS Stack FROM employees e

А в рекурсивной части - некоторые из значений изменяю, что делает их как бы "предыдущими" или назовем лучше рекурсивными, а другую часть значений оставил без изменений, сделав их корневыми :

UNION ALL -- recursive member expression SELECT e.Salary , e.ID , e.ManagerID -- порядок полей очень важен , c.RootSalary AS RootSalary , c.RootSalary - e.Salary AS RootSalaryDiff , c.RootID AS RootID , c.Stack + 1 AS Stack FROM employees e JOIN __cte c ON c.Id = e.ManagerID

Эти две части работают согласно принципам рекурсии и логике работы оператора FROM - в выражении выполняется вычитание "корневой" зарплаты с текущей (это всё что нас интересует в задаче), а оператор FROM сделает полный обход записей табличного выражения. Грубо говоря пройдется по всем сотрудникам.

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

SELECT c.ID AS ID , c.Salary AS Salary , c.RootID AS RootID -- , c.RootSalary AS RootSalary , c.RootSalaryDiff AS RootSalaryDiff -- , c.Stack AS Stack from __cte c where c.RootSalaryDiff < 0 order by id, RootID

Как и сказано выше - делается обход по записям сотрудников, при необходимости - выполняется объединение (рекурсия), а результат фильтруется по разнице зарплаты - она должна быть меньше нуля. То есть это индикатор что интересующий нас кадр получает на Х денег больше чем конкретный его начальник прямой или косвенный (RootID).

Результат выборки:

Трассируем. Для краткости "Сотрудник" обозначим С:

С1 получает 100 уе в его прямом подчинении С2, С4 которые получают 200 и 150 уе соответственно. Выборка говорит - С2 и С4 получают на 100 и 50 больше уе соответственно. косвенно ему подчиняются С3, С5 и С6, с окладами 150, 50 и 175 соответственно. Выборка - С3, С6 на 50 и 75 соответственно С3 получает 150 уе и у него только в прямом подчинении С5 и С6, с окладами 50 и 175. Выборка - С6 получает на 25 больше.

Постановка задачи удовлетворена

И полный код - бери запускай (на базе my_dev под SQL Server): https://pastebin.com/gt1vzHyt

ru.stackoverflow.com

Native-born citizen's blog: Иерархические/рекурсивные запросы (Oracle/MS-SQL)

Медленно и нерешительно продвигается портирование одной нашей разработки из-под Oracle на MS-SQL. Очередной маленькой победой стал перевод иерархического запроса в рекурсивный, почти совместимый с SQL 1999 (не считая слова RECURSIVE).Итак, в Oracle все просто – дерево в реляционной форме представляется как таблица, которая связывает каждый узел с его родителем.

parent child comment-----------------------------0 1 Root1 2 Level11 5 Level11 7 Level12 3 Level22 4 Level24 6 Level3…На самом деле, узлы могут идти в произвольном порядке. Чтобы обойти дерево последовательно, слева направо, достаточно отправить запрос следующего вида:

  1. SELECT parent, child, comment, level
  2. FROM tree
  3. CONNECT BY parent = PRIOR child
  4. START WITH parent = 0

Здесь level – идентификатор, которому сопоставляется текущий уровень иерархии, CONNECT BY указывает, по каким полям производится связывание, причем PRIOR определяет направление движения, а START WITH указывает на начало иерархии.Результатом данного запроса будет следующая таблица.

parent child comment-----------------------------0 1 Root1 2 Level12 3 Level22 4 Level24 6 Level31 5 Level21 7 Level2…Чтобы оформить те же действия для MS-SQL, строится следующий запрос.

  1. WITH
  2. Rec (child, parent, comment, level)
  3. AS (
  4. SELECT child, parent, comment, 0 AS level FROM tree WHERE parent = 0
  5. UNION ALL
  6. SELECT tree.child, tree.parent, tree.comment , level + 1
  7. FROM tree, Rec
  8. WHERE tree.parent = Rec.child
  9. )
  10. SELECT child, parent, comment, level FROM Rec

Я это понимаю так. С помощью фразы WITH мы строим новую таблицу Rec, из которой последний SELECT извлекает данные. Таблица Rec состоит из двух частей: корня иерархии (SELECT child, parent, comment, 0 AS level FROM tree where parent = 0), объединенного (UNION ALL) с преобразованным при помощи рекурсивного обхода телом базовой таблицы (второй SELECT). level приходится эмулировать базовыми средствами SQL.

nativeborncitizen.blogspot.com

Рекурсивные запросы CTE MS SQL Server

То, что мы называем «рекурсивным CTE», действительно должно называться итерационным CTE. Идея состоит в том, что для определения рекурсивной таблицы ( EmployeesCTE в этом случае) мы начинаем с создания некоторых начальных строк, в этом случае это делается

SELECT EmployeeID, FirstName + ' ' + LastName As EmployeeName, 1 As Rank FROM Employees WHERE ManagerID IS NULL

(обратите внимание, что это не содержит ссылки на EmployeesCTE поэтому оно не является рекурсивным), а затем мы повторяем выражение, в этом случае

SELECT Employees.EmployeeID, FirstName + ' ' + LastName, Rank + 1 FROM Employees JOIN EmployeesCTE ON Employees.ManagerID = EmployeesCTE.EmployeeID

для создания еще нескольких строк. Мы делаем это до тех пор, пока это выражение не будет возвращать строки. В этом выражении EmployeesCTE ссылается на предыдущую версию этой таблицы, и, оценивая ее, мы вычисляем следующую версию этой таблицы.

Таким образом, условие, которое останавливает рекурсию (или, скорее, итерацию), заключается в том, что рекурсивное выражение не дало новых строк.

Теперь давайте посмотрим, как все это относится к конкретному примеру, который вы дали. Наш начальный набор строк состоит из сотрудников, у которых нет менеджера (мы называем их ранга 1 сотрудников). Затем мы находим всех сотрудников, которым управляют сотрудники, найденные на предыдущем шаге (мы называем их рангами 2 сотрудника). Затем мы находим, что сотрудники управляются сотрудниками 2-го ранга и называют ранг 3, и так далее. В конце концов мы достигнем шага, где не найдутся новые сотрудники (предполагая, конечно, что управляемые отношения не имеют циклов).

Поскольку вы знакомы с C #, вы можете подумать об этом как о сложном объекте modell .

Представьте себе простой Windows.Forms.Form с его элементами управления. Каждый элемент управления имеет собственную коллекцию Controls. В базе данных вы можете подумать о таблице самореференций, где каждая строка указывает на ее родительскую строку (верхний объект указывает на NULL), например, ваши сотрудники указывают на следующий босс по иерархии.

Существует верхний объект с методом Refresh() . Когда вы вызываете это, функция делает что-то на своем собственном контенте и вызывает Refresh() в своей внутренней коллекции. Коллекция вызывает Refresh() для всех ее членов. Все они что-то делают и называют Refresh() в своих внутренних коллекциях. Это спустит вложенную модель до тех пор, пока вы не достигнете элементов управления с пустой коллекцией Controly.

Это больше похоже на верхний каскад . На самом деле может быть довольно сложно остановить рекурсивный CTE с условием, так как вы не получите последнюю строку, которая несет условие нарушения.

Вторая часть рекурсивного CTE подходит к естественному концу, когда операция JOIN не возвращает никаких строк …

В вашем случае вы можете прочитать это как

  • anchor: получить всех сотрудников, у которых нет босса (самый высокий уровень)
  • Теперь попросите список для всех сотрудников, у которых один из них является их менеджером (второй уровень)
  • Идите вниз по строкам и забирайте всех сотрудников, которые имеют второго уровня в качестве своего менеджера
  • Продолжайте, пока не будет более зависимых сотрудников

И имейте в виду, что рекурсивный CTE – по дизайну – медленный подход, поскольку это скрытый RBAR .

sqlserver.bilee.com

Вызов рекурсивного запроса T-SQL MS SQL Server

Вы в основном пересекаете график, который не ацикличен, поэтому вы должны явно избегать циклов. Один из способов – отслеживать путь на графике. Вот код, который должен работать. Вы также можете использовать тип данных HIERARCHYID SQL Server для хранения путей.

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

WITH cte(CarID,hier) AS ( SELECT CarID, CAST('/'+LTRIM(CarID)+'/' AS varchar(max)) FROM Car WHERE CarName = @StartCar UNION ALL SELECT c2.CarID, hier+LTRIM(c2.CarID)+'/' FROM Car AS c JOIN cte ON cte.CarID = c.CarID JOIN CarPart AS c1 ON c.CarID = c1.CarID JOIN CarPart AS c2 ON c2.PartName = c1.PartName WHERE hier NOT LIKE '%/'+LTRIM(c2.CarID)+'/%' ) SELECT c.CarName, cp.PartName FROM Car AS c JOIN CarPart AS cp ON cp.CarID = c.CarID JOIN cte on cte.CarID = c.CarID

SQL Fiddle

Запрос 1 :

declare @t table ( car_name varchar(100), part_name varchar(100) ) declare @car int = 3 insert @t select c.CarName, p.PartName from Car c join CarPart p on c.CarID = p.CarID where c.CarID = @car while exists( select c.CarName, p.PartName from Car c join CarPart p on c.CarID = p.CarID where c.CarName in ( select c.CarName from Car c join CarPart p on c.CarID = p.CarID where p.PartName in (select part_name from @t) and c.CarName not in (select car_name from @t) ) ) insert @t select c.CarName, p.PartName from Car c join CarPart p on c.CarID = p.CarID where c.CarName in ( select c.CarName from Car c join CarPart p on c.CarID = p.CarID where p.PartName in (select part_name from @t) and c.CarName not in (select car_name from @t) ) select * from @t

Результаты :

| CAR_NAME | PART_NAME | ------------------------ | Toyota | Door | | Toyota | Window | | Chevy | Engine | | Chevy | Door | | Hugo | Window | | Ford | Engine | | Ford | Wheel |

Причина, по которой ваш cte переходит в бесконечный цикл, состоит в том, что вы не определяете иерархию. Если вы нарисуете диаграмму отношений, вы увидите много кругов, которые заставляют любое прохождение к циклу навсегда.

Чтобы решить это, нужно создать иерархию. В моем коде первая cte car_hierarchy делает это, найдя все пары CarID но ограничивая, что левая должна быть меньше, чем правая. При этом у вас теперь есть диаграмма направленного отношения без кругов. (Если вы игнорируете направление, которое вы все еще можете найти в кругах, но это не имеет значения для алгоритма.)

Второй шаг – найти всех родственников для данного автомобиля. Поскольку данный автомобиль не может сидеть в конце иерархии, это двухэтапный процесс. Сначала найдите левую самую связанную машину, чем найдите оттуда весь подключенный автомобиль. car_left и car_right в запросе ниже делают именно это.

Последний шаг – взять идентификаторы и вытащить автомобиль и названия деталей еще в:

IF OBJECT_ID('dbo.Car') IS NOT NULL DROP TABLE dbo.Car CREATE TABLE dbo.Car ( CarID INT, CarName VARCHAR(16) ) IF OBJECT_ID('dbo.CarPart') IS NOT NULL DROP TABLE dbo.CarPart CREATE TABLE dbo.CarPart ( PartID INT, PartName VARCHAR(16), CarID INT ) INSERT INTO dbo.Car VALUES (1, 'Chevy'), (2, 'Ford'), (3, 'Toyota'), (4, 'Honda'), (5, 'Nissan'), (6, 'Hugo') INSERT INTO dbo.CarPart VALUES (110, 'Engine', 1), (120, 'Engine', 2), (210, 'Door', 1), (220, 'Door', 3), (310, 'Seat', 4), (320, 'Seat', 5), (410, 'Window', 3), (510, 'Wheel', 2), (420, 'Window', 6) DECLARE @StartCarID INT = 1; WITH car_hierachy (CarID1, CarID2) AS ( SELECT DISTINCT cp1.CarID CarID1, cp2.CarID CarID2 FROM dbo.CarPart cp1 JOIN dbo.CarPart cp2 ON cp1.PartName = cp2.PartName AND cp1.CarID < cp2.CarID ), car_left(CarID) AS ( SELECT @StartCarID UNION ALL SELECT ch.CarID1 FROM car_hierachy ch JOIN car_left cl ON cl.CarID = ch.CarID2 ), car_right(CarID) AS ( SELECT MIN(CarID) FROM car_left UNION ALL SELECT ch.CarID2 FROM car_hierachy ch JOIN car_right cr ON cr.CarID = ch.CarID1 ) SELECT * FROM car_right ac JOIN dbo.Car c ON ac.CarID = c.CarID JOIN dbo.CarPart cp ON c.CarID = cp.CarID ORDER BY c.CarId, cp.PartId;

SQLFiddle

Это должно решить вашу проблему. Однако я не уверен, что он будет хорошо работать. С большим набором данных вы могли бы лучше использовать цикл. Но с соответствующей индексацией это может сработать. Так что попробуй.

(Я переключил стартовый автомобиль с chevy на Toyota, чтобы показать, что он работает и для ars в середине иерархии. Если вы ходите только от Toyota, вы пропустите Ford.)

sqlserver.bilee.com

Рекурсивный запрос t-sql MS SQL Server

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

Данные приведены ниже.

Я хочу рекурсивно формировать полные пути из приведенных выше данных. Средство рекурсии даст следующий результат.

Вот пример CTE:

declare @t table (id int, name varchar(max), parentid int) insert into @t select 1, 'project' , 0 union all select 2, 'structure' , 1 union all select 3, 'path_1' , 2 union all select 4, 'path_2' , 2 union all select 5, 'path_3' , 2 union all select 6, 'path_4' , 3 union all select 7, 'path_5' , 4 union all select 8, 'path_6' , 5 ; with CteAlias as ( select id, name, parentid from @tt where t.parentid = 0 union all select t.id, parent.name + '\' + t.name, t.parentid from @tt inner join CteAlias parent on t.parentid = parent.id ) select * from CteAlias

Попробуйте что-то вроде этого:

WITH Recursive AS ( SELECT ID, CAST(PathName AS VARCHAR(500)) AS 'FullPaths', 1 AS 'Level' FROM dbo.YourTable WHERE ParentID = 0 UNION ALL SELECT tbl.ID, CAST(r.FullPaths + '\' + tbl.PathName AS VARCHAR(500)) AS 'FullPaths', r.Level + 1 AS 'Level' FROM dbo.YourTable tbl INNER JOIN Recursive r ON tbl.ParentID = r.ID ) SELECT * FROM Recursive ORDER BY Level, ID

Вывод:

ID FullPaths Level 1 project 1 2 project\structure 2 3 project\structure\path_1 3 4 project\structure\path_2 3 5 project\structure\path_3 3 6 project\structure\path_1\path_4 4 7 project\structure\path_2\path_5 4 8 project\structure\path_3\path_6 4

попробуй это:

DECLARE @YourTable table (id int, nameof varchar(25), parentid int) INSERT @YourTable VALUES (1,'project',0) INSERT @YourTable VALUES (2,'structure',1) INSERT @YourTable VALUES (3,'path_1',2) INSERT @YourTable VALUES (4,'path_2',2) INSERT @YourTable VALUES (5,'path_3',2) INSERT @YourTable VALUES (6,'path_4',3) INSERT @YourTable VALUES (7,'path_5',4) INSERT @YourTable VALUES (8,'path_6',5) ;WITH Rec AS ( SELECT CONVERT(varchar(max),nameof) as nameof,id FROM @YourTable WHERE parentid=0 UNION ALL SELECT CONVERT(varchar(max),r.nameof+'\'+y.nameof), y.id FROM @yourTable y INNER jOIN Rec r ON y.parentid=r.id ) select * from rec

вывод:

nameof ----------------------------------------------- project project\structure project\structure\path_1 project\structure\path_2 project\structure\path_3 project\structure\path_3\path_6 project\structure\path_2\path_5 project\structure\path_1\path_4 (8 row(s) affected)

Что-то вроде

;WITH MyCTE AS ( SELECT name AS FullPaths, id FROM MyTable WHERE parentid = 0 /*Normally it'd be IS NULL with an FK linking the 2 columns*/ UNION ALL SELECT C.FullPaths + '\' + M.name, M.id FROM MyCTE C JOIN MyTable M ON M.parentid = C.id ) SELECT FullPaths FROM MyCTE

Вам нужно будет изменить имя таблицы #test, которую я использовал.

WITH cte(id, name, parentid) AS ( SELECT id, convert(varchar(128), name), parentid FROM #test WHERE parentid = 0 UNION ALL SELECT t.id, convert(varchar(128), c.name +'\'+t.name), t.parentid FROM #test t INNER JOIN cte c ON c.id = t.parentid ) SELECT name as FullPaths FROM cte order by id

sqlserver.bilee.com

Как использовать рекурсивный запрос в качестве подзапроса? MS SQL Server

Мне нужно написать запрос, который вызывает рекурсивный запрос много раз.

Я не мог понять, как это сделать. Думаю, я могу это сделать, используя курсор, готовя инструкцию sql во время выполнения, а затем использую EXEC (mySQLstatement), чтобы запустить его на каждом курсоре FETCH NEXT.

Во всяком случае, это не очень хороший подход.

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

Таблица CUSTOMERS содержит поле ID_CUSTOMER и поле ID_PARENT_CUSTOMER, таблица CUSTOMER_CONTACTS содержит поле ID_CUSTOMER и поле ID_CONTACT.

С помощью этого запроса (он работает) я могу получить все контакты для клиента 308 и всех контактов для своих суб-клиентов:

with [CTE] as ( select ID_CUSTOMER from CUSTOMERS c where c.ID_CUSTOMER = 308 union all select c.ID_CUSTOMER from [CTE] p, CUSTOMERS c where c.ID_PARENT_CUSTOMER = p.ID_CUSTOMER ) select ID_CUSTOMER into #Customer308AndSubCustomers from [CTE] select 308 as ParentCustomer, ID_CUSTOMER, ID_CONTACT, from CUSTOMER_CONTACTS WHERE ID_CUSTOMER IN (select * from #Customer308AndSubCustomers) drop table #Customer308AndSubCustomers

Но я хотел бы иметь в одном запросе то же самое для ВСЕХ КЛИЕНТОВ, а не только для 308. Поэтому я предлагаю использовать курсор, чтобы я мог повторно использовать приведенный выше оператор и просто использовать переменную вместо 308.

Но можете ли вы предложить лучший запрос?

Solutions Collecting From Web of "Как использовать рекурсивный запрос в качестве подзапроса?"

Просто удалите условие фильтрации из анкерной части:

WITH q AS ( SELECT ID_CUSTOMER, ID_CUSTOMER AS root_customer FROM CUSTOMERS c UNION ALL SELECT c.ID_CUSTOMER, q.root_customer FROM q JOIN CUSTOMERS c ON c.ID_PARENT_CUSTOMER = q.ID_CUSTOMER ) SELECT * FROM q

root_customer покажет вам корень цепочки.

Обратите внимание, что одни и те же клиенты могут быть возвращены несколько раз.

Скажем, внук будет возвращен как минимум трижды: в его дереве дедушки, его родительском дереве и в собственном дереве, но каждый раз с другим root_customer .

sqlserver.bilee.com