Блог

Главная - Вопросы по программированию - Есть ли способ создать идентификатор группы для рекурсивного CTE в SSMS?

Есть ли способ создать идентификатор группы для рекурсивного CTE в SSMS?

#ssms #hierarchical-data #recursive-cte

#ssms #иерархические-данные #recursive-cte

Вопрос:

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

 rootID RootName RelatedName
1   ABA GPS
1   ABA PIG
1   ABA BBY
1   ABA PIG
2   PIG DDS
2   PIG GPS

Чего я пытаюсь достичь, так это столбца идентификатора группы, в котором данные могут выглядеть следующим образом:

 GroupID rootID  RootName RelatedName
100    1    ABA GPS
100    1    ABA PIG
100    1    ABA BBY
100    1    ABA PIG
100    2    PIG DDS
100    2    PIG GPS

и аналогично для группы 200, 300, … и т.д. для каждого дерева. Какую часть рекурсивного CTE можно ввести в код для достижения вышеуказанного результата?

 ;WITH cte_Rel AS (
SELECT
<columns>
FROM #RawRel r 
WHERE 1 = 1
AND <initial Conditions>
UNION ALL 
SELECT
<Columns>
FROM #RawRel r
JOIN cte_Rel c ON r.RootName = c.RelatedName
) 
SELECT DISTINCT * FROM cte_Rel
OPTION (MAXRECURSION 100)

Комментарии:

1. Откуда берется значение GroupID ? Поскольку это одно и то же значение, почему не просто 100 AS GroupID ?

2. В моих данных будет несколько деревьев… Итак, в приведенной выше демонстрации CTE сгенерирует это дерево 100, но также могут быть и другие деревья, для которых потребуется собственный groupId.

3. Поэтому мне понадобится умная стратегия увеличения в этом CTE или что-то в этом роде…

Ответ №1:

Вы можете добавить номер строки в привязочную часть рекурсивного CTE. Умножьте на 100 и повторите тот же столбец во второй части CTE.

Скрипка на случай, если вы предпочитаете интерактивный код.

Пример данных

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

 create table RelData
(
  ParentId int,
  Id int,
  Name nvarchar(3)
);

insert into RelData (ParentId, Id, Name) values
(null, 1, 'A00'), -- tree A
(1,    2, 'A10'),
(2,    3, 'A11'),
(2,    4, 'A12'),
(1,    5, 'A20'),
(5,    6, 'A21'),
(null, 7, 'B00'), -- tree B
(7,    8, 'B10'),
(8,    9, 'B11');

Решение

 WITH cte_Rel AS (
  SELECT row_number() over(order by rd.Id) * 100 as TreeId,  -- number to roots and multiply the root number by 100
         rd.Id, rd.Name, rd.ParentId, convert(nvarchar(3), null) as ParentName
  FROM RelData rd
  WHERE rd.ParentId is null
  
    UNION ALL
  
  SELECT c.TreeId,  -- repeat the tree number
         rd.Id, rd.Name, rd.ParentId, c.name
  FROM RelData rd
  JOIN cte_Rel c ON rd.ParentId = c.Id
) 
SELECT c.TreeId, c.ParentId, c.ParentName, c.Name
FROM cte_Rel c
where c.ParentId is not null
order by c.ParentId;

Результат

 TreeId ParentId ParentName Name
------ -------- ---------- ----
100    1        A00        A10
100    1        A00        A20
100    2        A10        A11
100    2        A10        A12
100    5        A20        A21
200    7        B00        B10
200    8        B10        B11