Bed ouin Ответов: 1

Sql CTE рекурсивный или курсор ?

У меня есть следующая таблица
ОТ | ДО
---------
P1 | P2
P2 | P3
P3 | P4
P4 | NULL
P5 | P3
P6 | P7
P7 | P6
Р1 дает Р2, но как Р2 дает Р3, Р1 косвенно дает Р3, но имеет Р3 дает Р4, Р1 косвенно дает Р4, так как Р4 никому не дает это конечный пункт назначения.

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

Основываясь на предыдущей таблице, я хочу создать финальную таблицу, которая будет
P1 | P4
P2 | P4
P3 | P4
P4 | NULL
P5 | P4
P6 | P7
P7 | P6

как лучше всего добиться рекурсивного CTE или цикла в функции с курсором?

Что я уже пробовал:

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

;WITH CTE AS (
select m1.FROM, m2.TO 
from Table m2 join Table m1 
on m2.FROM=m1.TO and m2.TO is not null and m2.TO<>m1.From
)
update TmpTable set TO = CTE.TO from CTE where Table.FROM = CTE.FROM

CHill60

Определенно избегайте курсора! "Лучший" - это не то слово, которое ассоциируется с курсорами ... обычно это "последнее средство" :-)
Я буду иметь правильный взгляд, когда освобожусь (я на работе), но это похоже на проблему иерархии, которую я использовал в своей статье Циклы обработки в SQL Server[^] - однако этот пример не обрабатывает ваше условие бесконечного цикла

1 Ответов

Рейтинг:
11

Richard Deeming

Возможно, есть более чистый способ сделать это, но это, кажется, работает:

WITH cteRawData As
(
    SELECT
        [From],
        [To],
        '/' + CAST([From] As varchar(max)) + '/' + CAST([To] As varchar(max)) + '/' As [Path],
        1 As Depth
    FROM
        [Table]
    
    UNION ALL
    
    SELECT
        F.[From],
        T.[To],
        F.[Path] + CAST(T.[To] As varchar(max)) + '/',
        F.Depth + 1
    FROM
        cteRawData As F
        INNER JOIN [Table] As T
        ON T.[From] = F.[To]
    WHERE
        F.[Path] Not Like '%/' + CAST(T.[To] As varchar(max)) + '/%'
),
cteData As
(
    SELECT
        [From],
        [To],
        ROW_NUMBER() OVER (PARTITION BY [From] ORDER BY Depth DESC) As RN
    FROM
        cteRawData
)
SELECT
    [From],
    [To]
FROM
    cteData
WHERE
    RN = 1
ORDER BY
    [From]
;

Maciej Los

О, нет! Это прекрасно!

CHill60

Вернулся, чтобы ответить на этот вопрос, и нашел ... это!
5 б

Bed ouin

Фантастика, работает как заклинание.
Блестящий.
Спасибо