搜索父子关系，直到父级满足条件

Question

我的表如下所示：

ChildPart    ParentPart    Quantity    ChildType
--------------------------------------------------
a0001        b0001         1           Bought
a0002        b0002         1           Bought
a0003        b0003         1           Bought
a0004        b0004         1           Bought
a0005        x0000         1           Made
b0001        c0001         1           Phantom
b0002        c0002         1           Phantom
b0003        x0000         1           External
b0004        c0004         1           Phantom
c0001        d0001         1           Phantom
c0002        x0000         1           External
c0004        d0004         1           Phantom
d0001        x0000         1           Made
e0004        x0000         1           External
x0000        x0000         1           Made

此表包含4个元素的父子关系。为了提供一些额外的细节，ChildType指定零件是由外部实体购买、制造还是制造的。我感兴趣的是只购买与父母相关的外部零件。但是，幻影状态应该被忽略，因为它是一个伪造的零件，只是为了跟踪最小的零件转换。

以下是对每个零件过程的更好说明：

part  a0001 -> b0001 -> c0001 -> d0001 -> x0000
type  Bought - Phanto - Phanto - Made   - Final Assembly (Made)
part  a0002 -> b0002 -> x0002 -> d0000
type  Bought - Phanto - Extern - Final Assembly (Made)
part  a0003 -> b0001 -> x0000  
type  Bought - Extern - Final Assembly (Made)
part  a0004 -> b0004 -> c0004 -> d0004 -> e0004 -> x0000
type  Bought - Phanto - Phanto - Phanto - Extern - Final Assembly (Made)
part  a0005 -> x0000
type  Bought - Final Assembly (Made)

我感兴趣的最终输出是一个表格，该表格将购买的零件（一开始提供的一套零件）和它们的父母联系起来，只要它们是外部的，并且绕过在中间的任何幻影。

如果零件到达了制造的父级（任何不是外部或幻影的其他零件），则应返回NULL或一个标志，指示该子级没有外部制造的父项。

我的意思是：

ChildPart    ExternalParent
-----------------------------
a0001        NULL
a0002        d0004        
a0003        c0004        
a0004        b0004        
a0005        NULL

我一直在尝试使用CTE，但没有任何运气。。。

这是我的密码。我打算将每个子项与其顶部外部处理的父项配对，然后选择MainChild和ExternalParent列。

    DECLARE @BOM TABLE(
    ChildPart VARCHAR(20)
    ParentPart VARCHAR(20)
    Quantity DEC(9,2)
    ChildType VARCHAR(20)
    )
    INSERT INTO @BOM VALUES
    ('a0001','b0001',1,'Bought')
    ,('a0002','b0002',1,'Bought')
    ,('a0003','b0003',1,'Bought')
    ,('a0004','b0004',1,'Bought')
    ,('a0005','b0005',1,'Made')
    ,('b0001','c0001',1,'Phantom')
    ,('b0002','c0002',1,'Phantom')
    ,('b0003','c0003',1,'External')
    ,('b0004','c0004',1,'Phantom')
    ,('c0001','d0001',1,'Phantom')
    ,('c0002','d0002',1,'External')
    ,('c0004','d0004',1,'Phantom')
    ,('d0001','e0001',1,'Made')
    ,('e0004','f0004',1,'External')
    ;
    DECLARE @partsToLook TABLE (ChildPart VARCHAR (20)
    INSERT INTO @partsToLook VALUES ('a0001'),('a0002'),('a0003'),('a0004'),('a0005')
    ----
    ;WITH cte AS 
    (
        SELECT  
            MainPart = p.ChildPart --This is to track the Main Child part we are looking the parents.
            ,ChildPart
            ,ParentPart
            ,Quantity
            ,ChildType
        FROM    @BOM b
        INNER JOIN @partsToLook p ON p.ChildPart=b.ChildPart
        UNION ALL
        SELECT  
            MainPart = tb.ChildPart
            ,ChildPart
            ,ParentPart
            ,Quantity
            ,ChildType
        FROM cte tb
        INNER JOIN @BOM b ON b.ChildPart=tb.ParentPart
    )
    SELECT MainPart,ParentPart FROM cte

Answer 1

您在问题中的预期结果与示例数据不完全匹配。你最好把它修好以免混淆。

我在递归查询中添加了一个显式StopRecursion标志，当递归到达一个不是'Bought', 'Phantom', 'External'的行时设置该标志。

然后ROW_NUMBER用于每个StartPart仅拾取一行，并且StopRecursion标志用于确定ExternalParent是否应当被设置为NULL。

样本数据

DECLARE @BOM TABLE(
    ChildPart VARCHAR(20)
    ,ParentPart VARCHAR(20)
    ,Quantity DEC(9,2)
    ,ChildType VARCHAR(20)
);
INSERT INTO @BOM (ChildPart,ParentPart,Quantity,ChildType) VALUES
 ('a0001','b0001',1,'Bought')
,('a0002','b0002',1,'Bought')
,('a0003','b0003',1,'Bought')
,('a0004','b0004',1,'Bought')
,('a0005','b0005',1,'Made')
,('b0001','c0001',1,'Phantom')
,('b0002','c0002',1,'Phantom')
,('b0003','c0003',1,'External')
,('b0004','c0004',1,'Phantom')
,('c0001','d0001',1,'Phantom')
,('c0002','d0002',1,'External')
,('c0004','d0004',1,'Phantom')
,('d0001','e0001',1,'Made')
,('e0004','f0004',1,'External')
;
DECLARE @partsToLook TABLE (ChildPart VARCHAR (20));
INSERT INTO @partsToLook (ChildPart) VALUES
('a0001'),
('a0002'),
('a0003'),
('a0004'),
('a0005');

查询

WITH
CTE
AS
(
    SELECT
        B.ChildPart
        ,B.ParentPart
        ,B.ChildType
        ,1 AS Lvl
        ,B.ChildPart AS StartPart
        ,CASE WHEN B.ChildType NOT IN ('Bought', 'Phantom', 'External') 
            THEN 1 ELSE 0 END AS StopRecursion
    FROM
        @BOM AS B
        INNER JOIN @partsToLook AS P ON P.ChildPart = B.ChildPart
    UNION ALL
    SELECT
        B.ChildPart
        ,B.ParentPart
        ,B.ChildType
        ,CTE.Lvl + 1 AS Lvl
        ,CTE.StartPart
        ,CASE WHEN B.ChildType NOT IN ('Bought', 'Phantom', 'External') 
            THEN 1 ELSE 0 END AS StopRecursion
    FROM
        @BOM AS B
        INNER JOIN CTE ON CTE.ParentPart = B.ChildPart
    WHERE
        CTE.StopRecursion = 0
)
,CTE_RN
AS
(
    SELECT
        StartPart
        ,ParentPart
        ,StopRecursion
        ,ROW_NUMBER() OVER (PARTITION BY StartPart ORDER BY Lvl DESC) AS rn
    FROM CTE
)
SELECT
    StartPart AS ChildPart
    ,CASE WHEN StopRecursion = 1 THEN NULL ELSE ParentPart END AS ExternalParent
FROM CTE_RN
WHERE rn = 1
ORDER BY ChildPart;

结果

+-----------+----------------+
| ChildPart | ExternalParent |
+-----------+----------------+
| a0001     | NULL           |
| a0002     | d0002          |
| a0003     | c0003          |
| a0004     | d0004          |
| a0005     | NULL           |
+-----------+----------------+