Как получить последнюю запись, созданную в рекурсивном CTE?

В приведенном ниже коде я использую рекурсивное выражение CTE (Common Table Expression) в SQL Server 2005, чтобы попытаться найти родительский элемент верхнего уровня базовой иерархической структуры. Правило этой иерархии состоит в том, что у каждого CustID есть ParentID, и если у CustID нет родителя, то ParentID = CustID, и это самый высокий уровень.

DECLARE @LookupID int

--Our test value
SET @LookupID = 1

WITH cteLevelOne (ParentID, CustID) AS
(
        SELECT   a.ParentID, a.CustID
        FROM     tblCustomer AS a
        WHERE    a.CustID = @LookupID
    UNION ALL
        SELECT   a.ParentID, a.CustID
        FROM     tblCustomer AS a
        INNER JOIN cteLevelOne AS c ON a.CustID = c.ParentID
        WHERE c.CustID <> a.CustomerID
)

Итак, если tblCustomer выглядит так:

ParentID    CustID
5            5
1            8
5            4
4            1

Результат, который я получил из приведенного выше кода:

ParentID    CustID
4            1
5            4
5            5

Я хочу только последнюю строку этого результата:

ParentID    CustID
5            5

Как мне просто вернуть последнюю запись, созданную в CTE (которая будет CustID самого высокого уровня)?

Также обратите внимание, что в этой таблице есть несколько несвязанных иерархий CustID, поэтому я не могу просто выполнить SELECT * FROM tblCustomer WHERE ParentID = CustID. Я не могу заказывать по ParentID или CustID, потому что номер идентификатора не связан с его местом в иерархии.


sql sql-server recursion common-table-expression
person JMcCon    schedule 29.08.2008    source источник

Ответы (3)


arrow_upward
2
arrow_downward

Если вы просто хотите получить максимальную глубину рекурсии, не могли бы вы сделать что-то подобное? Тогда, когда вы действительно запрашиваете CTE, просто ищите строку с max (Depth)? Вот так:

DECLARE @LookupID int

--Our test value
SET @LookupID = 1;

WITH cteLevelOne (ParentID, CustID, Depth) AS
(
        SELECT   a.ParentID, a.CustID, 1
        FROM     tblCustomer AS a
        WHERE    a.CustID = @LookupID
    UNION ALL
        SELECT   a.ParentID, a.CustID, c.Depth + 1
        FROM     tblCustomer AS a
        INNER JOIN cteLevelOne AS c ON a.CustID = c.ParentID 
        WHERE c.CustID <> a.CustID
)
select * from CTELevelone where Depth = (select max(Depth) from CTELevelone)

или, адаптируя то, что предлагает Тревор, это можно было бы использовать с тем же CTE:

select top 1 * from CTELevelone order by Depth desc

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

person AlexCuse    schedule 30.08.2008

arrow_upward
1
arrow_downward

Я не уверен, что полностью понимаю проблему, но просто чтобы взломать и разрезать ее, вы можете попробовать:

SELECT TOP 1 FROM cteLevelOne ORDER BY CustID DESC

Это предполагает, что CustID также в порядке, как в примере, а не что-то вроде GUID.

person Trevor Abell    schedule 29.08.2008

arrow_upward
0
arrow_downward

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

Msg 530, уровень 16, состояние 1, строка 15 Оператор завершен. Максимальное количество рекурсии 100 было исчерпано до завершения оператора.

person Naveen raj    schedule 11.07.2014