sql - 查找所有相关记录

Question

我有一个包含 LinkedOrderID 字段的 Order 表。

我想构建一个查询来查找所有链接的订单并在结果集中返回它们。

从 [Order] 中选择 OrderID,LinkOrderID 其中 LinkOrderID 不为空

订单 ID 链接订单 ID
787016 787037
787037 787786
787786 871702

我想要一个返回以下内容的存储过程:
订单 ID 继承订单
787016 1
787037 2
787786 3
871702 4

我还想确保我没有无限循环

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

这假设 OrderID和 LinkOrderID是唯一的(即它是一个链表，而不是一棵树)。

也适用于最后一个未注释的插入(这会形成一个循环)