Ms sql рекурсивный запрос: Изучение SQL: рекурсивные запросы

Содержание

sql server — рекурсивный запрос mssql

Вопрос задан
5 лет 3 месяца назад

Изменён
5 лет 3 месяца назад

Просмотрен
619 раз

Помогите с рекурсивным запросом к таблице, чтобы значение в столбце flag родителей был изменен 0 на 1 в случае если у всех его детей стоит 1

id  name    ID_PP   parentID    flag
1   Все NULL    NULL    0
2   Компания    NULL    1   0
3   Производство 1  NULL    2   0
4   Производство 2  NULL    2   0
5   Производство 3  NULL    2   0
6   Производство 4  NULL    2   0
7   Производство 5  NULL    2   0
8   Производство 6  NULL    2   0
9   Цех 1   NULL    3   0
10  Цех 2   NULL    4   0
11  Цех 3   NULL    4   0
12  Цех 4   NULL    4   0
13  Цех 5   NULL    5   0
14  Цех 6   NULL    5   0
15  Цех 7   NULL    6   0
16  Цех 8   NULL    6   0
17  Цех  9  NULL    6   0
18  Цех  10 NULL    7   0
19  Цех 11  NULL    7   0
20  Цех 12  NULL    8   1
21  Цех 13  8   1
23  Цех 14  NULL    8   1
24  Отделение 0101  1898    9   1
25  Отделение 0102  2205    9   0
26  Отделение 0103  2064    9   1
27  Отделение 0104  2121    9   0
28  Отделение 0105  2185    9   0
29  УСК             1983            9   0
30  Отделение 0106 (И-7)    5347    9   1
declare @in_bdt datetime = cast('13. 02.2018 00:00:00' as datetime)   
declare @in_edt datetime = cast('13.02.2018 23:59:00' as datetime) 
 declare @razn varchar(10) = DATEDIFF (HOUR,@in_bdt,@in_edt)+1
;with rec (  [id]
      ,[ID_PP]
      ,[name]
      ,[parentID]
      ,flag)
  as ( 
  select [id]
      ,t2.[ID_PP]
      ,[name]
      ,[parentID]
      ,flag
   from [dbo].[tab1] 
    left  join(
           SELECT IIF((COUNT([ID_PP])-@razn)=0,1,0) flag
           ,[ID_PP]
                 FROM [dbo].[tab2]
                 where dt between @in_bdt and @in_edt
                 group by  [ID_PP]
           ) as t2 on [tab1].ID_PP = t2.ID_PP
   union all
   select [tab1].[id]
      ,[tab1].[ID_PP]
      ,[tab1].[name]
      ,[tab1].[parentID]  
      ,flag
            from [tab1] 
      join rec on [tab1].id =rec.parentID
   )
select distinct rec.[id]
      ,[ID_PP]
      ,[name]
      ,[parentID]
      ,flag
       from rec
       order by rec.[id]
1   NULL    1
2   1   1
3   2   1
4   2   1
5   2   1
6   2   1
7   2   1
8   2   1
83  2   1
9   3   1
10  4   NULL
11  4   1
12  4   1
13  5   1
14  5   1
15  6   1
16  6   1
17  6   1
18  7   1
19  7   1
20  8   1
21  8   1
23  8   1
24  9   1
25  9   NULL

sql-server
запрос
рекурсия

with RTable as(
     select [id],t2. [ID_PP],[name],[parentID], coalesce(flag,0) flag
       from [dbo].[tab1] 
       left  join(
           SELECT IIF((COUNT([ID_PP])-@razn)=0,1,0) flag,[ID_PP]
             FROM [dbo].[tab2]
             where dt between @in_bdt and @in_edt
             group by  [ID_PP]
       ) as t2 on [tab1].ID_PP = t2.ID_PP
),
R1 as(
    select id, ParentId, flag
      from RTable R
     where not exists(select 1 from RTable N where N.parentId=R.Id)
    union all
    select T.id, T.ParentId,
           case when T.flag=1 then 1 else R.flag end
      from R1 R, RTable T
     where T.id=R.ParentId
)
select id, parentId, min(flag) flag
  from R1
group by id, parentId

Спускаемся от листьев к корню дерева (лист это такая запись у которой нет наследников). При этом берем флаг из текущей записи, если он 1 или, если он 0, то берем флаг пришедший по рекурсии от наследника. Если по всем возможным путям (от всех листьев) до данного узла дошли только 1, то и итогом данного узла будет 1. На выходе из рекурсии у нас как раз для каждого id есть записи всех его прямых наследников с теми флагами, которые были получены спуском от листьев. Остается только сгруппировать до id и получить минимум (если был хотя бы один 0 — то итог 0)

Пример на sqlfiddle.com (только рекурсии, без начальной подготовки таблицы из вашего примера)

Зарегистрируйтесь или войдите

Регистрация через Google

Регистрация через Facebook

Регистрация через почту

Отправить без регистрации

Почта

Необходима, но никому не показывается

Отправить без регистрации

Почта

Необходима, но никому не показывается

Нажимая на кнопку «Отправить ответ», вы соглашаетесь с нашими пользовательским соглашением, политикой конфиденциальности и политикой о куки

sql — Как написать рекурсивный запрос?

Кратко опишу алгоритм.

Если я правильно понял, задача состоит из трёх частей:

От переданного ИДа идём вверх по дереву, пока не найдём департамент.
от департамента находим всех его детей, выбираем из них только с типом employee
выбираем тех, кто есть в таблице cache_extensions

пункты 1. и 2. первая и вторая рекурсивная часть рекурсивного СТЕ:

—таблички

USE tempdb;
IF OBJECT_ID('dept_employees')IS NOT NULL DROP TABLE dept_employees
IF OBJECT_ID('employees')IS NOT NULL DROP TABLE employees
IF OBJECT_ID('ogpo_dept')IS NOT NULL DROP TABLE ogpo_dept
IF OBJECT_ID('cash_extensions')IS NOT NULL DROP TABLE cash_extensions
CREATE TABLE dept_employees(
  ID INT NOT NULL,
  parent_id INT NULL,
  obj_id INT NOT NULL,
  obj_type VARCHAR(4) NOT NULL
)
CREATE TABLE employees(
  ID INT NOT NULL,
  FIO VARCHAR(255) NOT NULL,
  dept_id INT NOT NULL
)
/*CREATE TABLE ogpo_dept(
  ID INT NOT NULL,
  Name VARCHAR(255) NOT NULL
)*/
CREATE TABLE cash_extensions(
  ID INT NOT NULL,
  FIO VARCHAR(255) NOT NULL
)

—данные

INSERT dept_employees VALUES
--depts
(10, NULL, 1, 'dept'),
(20, 10,    2, 'dept'),
(30, NULL, 3, 'dept'),
(40, 30,    4, 'dept'),
--emps
(50, 20, 1, 'emp'),
(60, 50, 2, 'emp'),
(70, 50, 3, 'emp'),
(80, 40, 4, 'emp'),
(90, 80, 5, 'emp'),
(100,30, 6, 'emp')
INSERT employees VALUES
(1, 'd1->d2->p1', 2),
(2, 'd1->d2->p1->p2', 2),
(3, 'd1->d2->p1->p3', 2),
(4, 'd3->d4->p4', 4),
(5, 'd3->d4->p4->p5', 4),
(6, 'd3->p6', 3)
INSERT cash_extensions VALUES
(1, 'd1->d2->p1'),
(2, 'd1->d2->p1->p2'),
(3, 'd1->d2->p1->p3'),
(4, 'd3->d4->p4'),
(5, 'd3->d4->p4->p5'),
(6, 'd3->p6')

—процедура

IF OBJECT_ID('FindAllCasheExtemsionsFromDeptByEmployee') IS NOT NULL DROP PROC FindAllCasheExtemsionsFromDeptByEmployee
GO
CREATE PROC FindAllCasheExtemsionsFromDeptByEmployee 
  @EmployeeId INT
AS
WITH CTE AS(
  SELECT 
    CASE obj_type 
      WHEN 'emp' THEN 'find_depart' 
      WHEN 'dept' THEN 'find_childs'
    END what, id AS child_Id, parent_id AS parend_id, obj_id, E. obj_type
  FROM dept_employees E
  WHERE id = @EmployeeId
  UNION ALL
  --идём вверх по дереву до первого департамента
  SELECT 
    CASE E.obj_type 
      WHEN 'emp' THEN 'find_depart' 
      WHEN 'dept' THEN 'find_childs'
    END what, id AS child_Id, parent_id AS parend_id, E.obj_id, E.obj_type
  FROM CTE
    JOIN dept_employees E ON CTE.parend_id = E.ID
  WHERE what = 'find_depart'
  UNION ALL
  --теперь идём вниз по дереву, находим всех потомков
  SELECT 'find_childs' what, id AS child_Id, parent_id AS parend_id, E.obj_id, E.obj_type
  FROM CTE
    JOIN dept_employees E ON CTE.child_id = E.parent_id
  WHERE what = 'find_childs'
)
SELECT E.*
FROM CTE
  JOIN employees E ON CTE.obj_id = E.ID
  JOIN cash_extensions C ON E.FIO = C.FIO
WHERE CTE.obj_type = 'emp' AND CTE.what = 'find_childs'
OPTION (MAXRECURSION 0) 
GO

—тесты и результаты

EXEC FindAllCasheExtemsionsFromDeptByEmployee 10 -- d1
/*
ID     FIO    dept_id
1      d1->d2->p1   2
2      d1->d2->p1->p2      2
3      d1->d2->p1->p3      2
*/
EXEC FindAllCasheExtemsionsFromDeptByEmployee 50 -- p1
/*
ID     FIO    dept_id
1      d1->d2->p1   2
2      d1->d2->p1->p2      2
3      d1->d2->p1->p3      2
*/
EXEC FindAllCasheExtemsionsFromDeptByEmployee 70 -- p3
/*
ID     FIO    dept_id
1      d1->d2->p1   2
2      d1->d2->p1->p2      2
3      d1->d2->p1->p3      2
*/
EXEC FindAllCasheExtemsionsFromDeptByEmployee 40 -- d4
/*
ID     FIO    dept_id
4      d3->d4->p4   4
5      d3->d4->p4->p5      4
*/
EXEC FindAllCasheExtemsionsFromDeptByEmployee 90 -- p5
/*
ID     FIO    dept_id
4      d3->d4->p4   4
5      d3->d4->p4->p5      4
*/
EXEC FindAllCasheExtemsionsFromDeptByEmployee 100 -- p6
/*
ID     FIO    dept_id
4      d3->d4->p4   4
5      d3->d4->p4->p5      4
6      d3->p6 3
*/

пс: задача очень плохо описана.

общее табличное выражение — SQL Server CTE и пример рекурсии

Я не тестировал ваш код, просто попытался помочь вам понять, как он работает, в комментарии;

 С
  cteReports (EmpID, Имя, Фамилия, MgrID, EmpLevel)
  КАК
  (
-->>>>>>>>>>Блок 1>>>>>>>>>>>>>>>>>>>>
-- В rCTE этот блок называется [Anchor].
-- Запрос находит все корневые узлы, как описано в WHERE ManagerID IS NULL.
    ВЫБЕРИТЕ EmployeeID, Имя, Фамилия, ManagerID, 1
    ОТ сотрудников
    ГДЕ ManagerID имеет значение NULL
-->>>>>>>>>>Блок 1>>>>>>>>>>>>>>>>>>>>
    СОЮЗ ВСЕХ
-->>>>>>>>>>Блок 2>>>>>>>>>>>>>>>>>>>
-- Это рекурсивное выражение rCTE
-- При первом "выполнении" он будет запрашивать данные в [Employees],
-- относительно [Anchor] выше.
-- Это создаст набор результатов, мы назовем его R{1}, и он будет ПРИСОЕДИНЕН к [Сотрудникам].
-- как определено иерархией
-- Последующие "выполнения" этого блока будут ссылаться на R{n-1}
    ВЫБЕРИТЕ e.EmployeeID, e.FirstName, e.LastName, e.ManagerID,
      r. EmpLevel + 1
    ОТ сотрудников e
      ВНУТРЕННЕЕ СОЕДИНЕНИЕ cteReports r
        ON e.ManagerID = r.EmpID
-->>>>>>>>>>Блок 2>>>>>>>>>>>>>>>>>>>
  )
ВЫБИРАТЬ
  Имя + ' ' + Фамилия КАК ФИО,
  ЭмпУровень,
  (ВЫБЕРИТЕ Имя + '' + Фамилия ОТ Сотрудников
    ГДЕ EmployeeID = cteReports.MgrID) AS Manager
ИЗ cteReports
ЗАКАЗАТЬ ПО EmpLevel, MgrID

Самый простой пример рекурсивного CTE , который я могу придумать, чтобы проиллюстрировать его работу;

 ;С номерами КАК
(
    ВЫБЕРИТЕ n = 1
    СОЮЗ ВСЕХ
    ВЫБЕРИТЕ п + 1
    ОТ Числа
    ГДЕ n+1 <= 10
)
ВЫБЕРИТЕ n
ОТ Числа

В 1) как увеличивается значение N. если значение присваивается N каждый раз, то значение N может увеличиваться, но значение N инициализируется только в первый раз .

A1: В этом случае N не является переменной. N — псевдоним. Это эквивалент SELECT 1 AS N . Это синтаксис личных предпочтений. Существует 2 основных метода псевдонимов столбцов в CTE в T-SQL . Я включил аналог простого CTE в Excel , чтобы попытаться более привычно проиллюстрировать происходящее.

 -- Снаружи
;С CTE (MyColName) КАК
(
    ВЫБЕРИТЕ 1
)
-- Внутри
;С ОТЭ КАК
(
    ВЫБЕРИТЕ 1 КАК MyColName
    -- Или
    ВЫБЕРИТЕ MyColName = 1
    -- И т. д...
)

Q 2) теперь здесь о CTE и рекурсии отношений сотрудников
в тот момент, когда я добавляю двух менеджеров и добавляю еще несколько сотрудников под вторым менеджером, тогда начинаются проблемы.
я хочу отобразить информацию о первом менеджере, а в следующих строках будут отображаться только данные о сотрудниках, которые являются подчиненными этого менеджера

A2:

Этот код отвечает на ваш вопрос?

 -----------------------------------------------------------
-- Синтезировать таблицу с нерекурсивным CTE
--------------------------------------------
;С сотрудником (ID, имя, MgrID) AS
(
    ВЫБЕРИТЕ 1, 'Кит', NULL UNION ALL
    ВЫБЕРИТЕ 2, 'Джош', 1 ОБЪЕДИНИТЕ ВСЕ
    ВЫБЕРИТЕ 3, 'Робин', 1 ОБЪЕДИНЕНИЕ ВСЕХ
    ВЫБЕРИТЕ 4, 'Раджа', 2 ОБЪЕДИНИТЕ ВСЕ
    ВЫБЕРИТЕ 5, 'Тридип', NULL UNION ALL
    ВЫБЕРИТЕ 6, 'Ариджит', 5 ОБЪЕДИНИТЕ ВСЕ
    ВЫБЕРИТЕ 7, 'Амит', 5 ОБЪЕДИНИТЕ ВСЕ
    ВЫБЕРИТЕ 8, 'Разработчик', 6
)
--------------------------------------------
-- Рекурсивный CTE - привязан к указанному выше CTE
--------------------------------------------
,Иерархия КАК
(
    --  Якорь
    ВЫБЕРИТЕ ИДЕНТИФИКАТОР
            ,Имя
            ,ID администратора
            ,nУровень = 1
            ,Family = ROW_NUMBER() OVER (ЗАКАЗАТЬ ПО ИМЕНИ)
    ОТ Сотрудника
    ГДЕ MgrID имеет значение NULL
    СОЮЗ ВСЕХ
    -- Рекурсивный запрос
    ВЫБЕРИТЕ Э. ID
            ,E.Имя
            ,Э.МгрИД
            ,H.nLevel+1
            ,Семья
    ОТ Сотрудника E
    ПРИСОЕДИНЯЙТЕСЬ к иерархии H ON E.MgrID = H.ID
)
ВЫБИРАТЬ *
ИЗ Иерархии
ЗАКАЗАТЬ ПО Семейству, nLevel

 ВЫБЕРИТЕ ID,пробел(nLevel+
                    (СЛУЧАЙ, КОГДА nLevel > 1, THEN nLevel ELSE 0 END)
                )+Имя
ИЗ Иерархии
ЗАКАЗАТЬ ПО Семейству, nLevel

sql - Рекурсивный запрос с отношением родитель-потомок

Задавать вопрос

спросил
1 год, 10 месяцев назад

Изменено
1 год, 10 месяцев назад

Просмотрено
4к раз

Я пытаюсь сделать рекурсивный запрос в SQL Server, отображающий данные иерархически. Вот структура таблицы

 [id] [int] IDENTITY(1,1) NOT NULL,
    [имя] [varchar(100)] НЕ NULL,
    [Parent_Id] [число] NULL,

У каждого продукта есть родитель. Столбец Parent_Id содержит идентификатор родителя. parent_id имеет значение null для корневых продуктов.

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

Продукты могут иметь дочерние элементы.

Для приведенного выше рисунка результат запроса должен выглядеть следующим образом:

 id name parent_id
1 P1 НОЛЬ
2 P2 НОЛЬ
3 П2-1 2
4 П2-2 2
5 П2-3 2
6 П2-3-1 5
7 П2-3-2 5
8 Р3 НОЛЬ
9 П3-1 8

Вот запрос, который я написал для его достижения:

 с деревом как (выберите * из продуктов
               союз всех
               выберите * из дерева, где parent_id = tree.id
             )
выбрать * из дерева;

Но я получаю результат, подобный следующему:

 1 P1 NULL
2 P2 НОЛЬ
8 Р3 НОЛЬ
3 П2-1 2
4 П2-2 2
5 П2-3 2
9 П3-1 8
6 П2-3-1 5
7 П2-3-2 5

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

sql
sql-сервер
tsql
иерархические данные
рекурсивный запрос

Просто еще один вариант с использованием типа данных иерархия

Существуют некоторые дополнительные возможности и функции, связанные с иерархией

Пример

 -- Опционально См. 1-й ГДЕ
Объявить @Top int = null --<< Устанавливает верхнюю часть Hier Try 2
;с cteP как (
      Выберите идентификатор
            ,parent_id
            ,Имя
            , HierID = convert (иерархический идентификатор, concat ('/', ID, '/'))
      Из вашего стола
      Где IsNull(@Top,-1) = случай, когда @Top имеет значение null, тогда isnull(parent_id,-1), иначе конец идентификатора
      --Where parent_id is null -- Используйте это, если вы всегда хотите видеть полную иерархию
      Союз Все
      Выберите ID = r.ID
            ,parent_id = r.parent_id
            ,Имя = р. Имя
            ,HierID = convert(hierarchyid,concat(p.HierID.ToString(),r.ID,'/'))
      Из вашей таблицы r
      Присоединяйтесь к cteP p на r.parent_id = p.ID)
Выберите Lvl = HierID.GetLevel()
      ,ИДЕНТИФИКАТОР
      ,parent_id
      ,Имя
 Из КТЭП А
 Заказ по A.HierID

Результаты

 Lvl ID parent_id Имя
1 1 НУЛЕВОЕ P1
1 2 НУЛЕВОЕ P2
2 3 2 П2-1
2 4 2 П2-2
2 5 2 П2-3
3 6 5 П2-3-1
3 7 5 П2-3-2
1 8 НУЛЕВОЕ P3
2 9 8 П3-1

Просто для удовольствия. Если я установлю @Top на 2, результатом будет

 Lvl ID parent_id Имя
1 2 НУЛЕВОЕ P2
2 3 2 П2-1
2 4 2 П2-2
2 5 2 П2-3
3 6 5 П2-3-1
3 7 5 П2-3-2

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

с деревом как (
выберите p.id, p.name, p.parentid,
формат (p.parentid, '0000') как путь
из продуктов р
где p.