Блог

Главная - Вопросы по программированию - Рекурсивно получить последнюю запись из той же родительской дочерней таблицы

Рекурсивно получить последнюю запись из той же родительской дочерней таблицы

#sql #sql-server #sql-optimization

#sql #sql-сервер #sql-оптимизация

Вопрос:

Я хочу получить идентификатор последней ссылки из той же таблицы. У меня есть следующая таблица.

  ID   UserId   DelegatedToUserId
 1      100       101
 2      101       102
 3      102       103
 4      103       NULL
 5      104       109

Я просто не могу прийти в себя. Я знаю, чего я хочу, просто просто не могу вывести это на экран. Поэтому, когда я запрашиваю 100, запрос должен возвращать 103, когда я снова запрашиваю 101 или 102, он должен возвращать 103. когда пользователь вводит 104, он должен возвращать 109

И когда я запрашиваю 103, он должен возвращать 103, поскольку для этого нет делегата.

можно ли это сделать в одном sql-запросе?

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

1. «можно ли это сделать в одном sql-запросе?» Да, обычным способом является использование рекурсивного выражения Common Table (rCTE). Вы использовали их раньше или проводили какие-либо исследования по ним?

2. Кроме того, запрос не вернет никаких строк, если они будут введены 104 , поскольку в вашей таблице нет пользователя 109 . Поскольку пользователь 104 не имеет родителя ( 109 не существует), он является сиротой, и поэтому, когда вы возвращаете «верхнюю родительскую строку», которая не будет существовать. Это отличается от 103 , поскольку в нем конкретно указано, что у него нет родительского элемента; его можно идентифицировать как «корень».

Ответ №1:

можно ли это сделать в одном sql-запросе?

Если вы не знаете, до какого уровня может доходить иерархия, вам нужно использовать рекурсивный CTE . Если это всего лишь 1 или 2 уровень, вы можете сделать это без рекурсии CTE в одном запросе.

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

 ;WITH mytest 
     AS (SELECT P.userid, 
                P.delegatedtouserid, 
                1 AS LVL 
         FROM   @table P 
         WHERE  userid = 100 
         UNION ALL 
         SELECT P1.userid, 
                P1.delegatedtouserid, 
                M.lvl   1 AS LVL 
         FROM   @table P1 
                INNER JOIN mytest M 
                        ON M.delegatedtouserid = P1.userid) 

SELECT TOP 1 WITH ties * 
FROM   mytest 
ORDER  BY Row_number() OVER (ORDER BY lvl DESC);

Примечание: Замените @table на ваше фактическое имя таблицы.

Онлайн-демонстрация

Ответ №2:

Лично я бы выбрал это:

 DECLARE @UserID int = 103;

WITH VTE AS
    (SELECT *
     FROM (VALUES (1, 100, 101),
                  (2, 101, 102),
                  (3, 102, 103),
                  (4, 103, NULL),
                  (5, 104, 109)) AS V (ID, UserID, DelegatedToUserID) ),
rCTE AS
    (SELECT V.ID,
            V.UserID,
            V.DelegatedToUserID
     FROM VTE V
     WHERE UserID = @UserID
     UNION ALL
     SELECT V.ID,
            V.UserID,
            V.DelegatedToUserID
     FROM rCTE r
          JOIN VTE V ON r.DelegatedToUserID = V.UserID)
SELECT *
FROM rCTE
WHERE rCTE.DelegatedToUserID IS NULL;

Однако, как я упоминал в комментариях, передача 104 не вернет ни одной строки, поскольку пользователь 109 не существует в таблице.