Trouver tous les enregistrements liés

Question

J'ai une table Order qui possède un champ LinkedOrderID.

Je voudrais construire une requête qui trouve toutes les commandes liées et les renvoie dans le jeu de résultats.

select OrderID,LinkOrderID from [Order] where LinkOrderID is not null

OrderID LinkOrderID
787016 787037
787037 787786
787786 871702

Je voudrais une procédure stockée qui renvoie le résultat suivant :
OrderID InheritanceOrder
787016 1
787037 2
787786 3
871702 4

Je voudrais aussi m'assurer que je n'ai pas de boucle infinie.

Answer 1

DECLARE @Order TABLE (OrderID INT NOT NULL, LinkOrderID  INT NOT NULL)

INSERT
INTO    @Order
VALUES  (787016, 787037)

INSERT
INTO    @Order
VALUES  (787037, 787786)

INSERT
INTO    @Order
VALUES  (787786, 871702)

/*
INSERT
INTO    @Order
VALUES  (871702, 787016)
*/

;WITH    q (OrderId, LinkOrderId, InheritanceOrder, FirstItem) AS
        (
        SELECT  OrderID, LinkOrderId, 1, OrderID
        FROM    @Order
        WHERE   OrderID = 787786
        UNION ALL
        SELECT  o.OrderId, o.LinkOrderId, q.InheritanceOrder + 1, q.FirstItem
        FROM    q
        JOIN    @Order o
        ON      o.OrderID = q.LinkOrderId
        WHERE   o.OrderID <> q.FirstItem
        UNION ALL
        SELECT  LinkOrderId, NULL, q.InheritanceOrder + 1, q.FirstItem
        FROM    q
        WHERE   q.LinkOrderID NOT IN
                (
                SELECT  OrderID
                FROM    @Order
                )
        )
SELECT  OrderID, InheritanceOrder
FROM    q
ORDER BY
        InheritanceOrder

Cela suppose que les deux OrderID y LinkOrderID sont uniques (c'est-à-dire que c'est une liste chaînée, pas un arbre).

Fonctionne également avec le dernier insert décommenté (ce qui crée une boucle).

Answer 2

Pour vérifier l'existence d'une boucle infinie, il y a deux vérifications :

Tout d'abord, assurez-vous que vous commencez avec un _id qui n'apparaît jamais dans la base de données de l'entreprise. LinkOrderID

select o1.OrderID from Order o1
left outer join Order o2 on o1.OrderId = o2.LinkOrderID
where o2.LinkOrderID is null;

Cela vous donnera une liste qui sont les débuts de vos listes liées.

Ensuite, assurez-vous qu'aucun de vos _id n'apparaisse plus d'une fois.

select * from {
  select LinkOrderId, count(*) as cnt from Order
} where cnt > 1;

Si ces deux conditions sont vraies (vous partez d'un ordre qui n'apparaît jamais dans la liste des ordres liés et vous n'avez pas d'ID d'ordre qui sont liés plusieurs fois), vous devez alors no puede ont une boucle.

Answer 3

Super : j'ai trouvé la solution grâce au code génial de Quassnoi. Je l'ai modifié pour qu'il remonte d'abord jusqu'au parent le plus ancien, puis qu'il descende à travers tous les enfants. Merci encore !

-- =============================================
-- Description: Gets all LinkedOrders for OrderID. 
-- It will first walk up and find oldest linked parent, and then next walk down recursively and find all children.
-- =============================================
alter PROCEDURE Order_Order_GetAllLinkedOrders
(
    @StartOrderID int
)
AS
--Step#1: find oldest parent
DECLARE @oldestParent int
;WITH    vwFirstParent (OrderId) AS 
        ( 
        SELECT  OrderID
        FROM    [Order]
        WHERE   OrderID = @StartOrderID 
        UNION ALL 
        SELECT  o.OrderId
        FROM    vwFirstParent
        JOIN    [Order] o 
        ON      o.LinkOrderID = vwFirstParent.OrderId 
        )

select @oldestParent = OrderID from vwFirstParent 

--Step#2: find all children, prevent recursion
;WITH    q (OrderId, LinkOrderId, InheritanceOrder, FirstItem) AS 
        ( 
        SELECT  OrderID, LinkOrderId, 1, OrderID 
        FROM    [Order]
        WHERE   OrderID = @oldestParent 
        UNION ALL 
        SELECT  o.OrderId, o.LinkOrderId, q.InheritanceOrder + 1, q.FirstItem 
        FROM    q 
        JOIN    [Order] o 
        ON      o.OrderID = q.LinkOrderId 
        WHERE   o.OrderID <> q.FirstItem 
        UNION ALL 
        SELECT  LinkOrderId, NULL, q.InheritanceOrder + 1, q.FirstItem 
        FROM    q
        WHERE   q.LinkOrderID NOT IN 
                ( 
                SELECT  OrderID 
                FROM    [Order]
                )
        ) 
SELECT  OrderID,LinkOrderId,  InheritanceOrder
FROM    q