使用临时表 + 循环展 BOM

  使用临时表 + 循环展 BOM 

背景

有如下的 BOM 表，parent_part 与 child_part 是多对多的关系，现在要求查询出每个 child_part 的最顶层的 parent_part

CREATE TABLE pl_bom(

    parent_part varchar(10),

    child_part varchar(10))

INSERT INTO pl_bom

SELECT 'A','C' UNION ALL

SELECT 'B','C' UNION ALL

SELECT 'C','D' UNION ALL

SELECT 'D','E' UNION ALL

SELECT 'D','F' UNION ALL

SELECT 'H','F'

       即根据上述数据，应该得到如下结果

child_part parent_part

---------- -----------

C          A

C          B

D          A

D          B

E          A

E          B

F          A

F          B

F          H

 

处理方法

       一般可能会考虑从 child_part 开始扫描的方法。但对于每个 child_part 而言，它有一至多个 parent_part，对于多个parent_part 而言，每个 parent_part 到最顶部的 parent_part 经过的层数还可能不一致，这会导致扫描算法不太好写，而且同一个 parent_part 如果被多个 child_part 引用的话， 还可能导致重复的搜索此 parent_part 的顶 parent_part。

下面的算法采用自 parent_part 反推 child_part 的方式，可以避免重复扫描某个 parent_part 到 child_part 的问题

-- 使用自顶向下展开

-- 因为要删除数据, 所以不能用原始表, 用个临时表

SELECT

    id = IDENTITY(int, 1,1 ), child_part, parent_part

INTO #

FROM pl_bom

 

-- 从顶往下展

DECLARE @Level int

SET @Level = 1

SELECT

    id = id * 1, Level = @Level,

    child_part, parent_part

INTO #re

FROM # A

WHERE NOT EXISTS(

        SELECT * FROM #

        WHERE child_part = A.parent_part)

WHILE @@ROWCOUNT > 0

BEGIN

    SET @Level = @Level + 1

    DELETE A

    FROM # A, #re B

    WHERE A.id = B.id

        AND B.Level = @Level - 1

 

    INSERT #re(

        id, Level,

        child_part, parent_part)

    SELECT

        A.id, @Level,

        A.child_part, B.parent_part

    FROM # A, #re B

    WHERE A.parent_part = B.child_part

        AND B.Level = @Level - 1

END

 

-- 显示结果

SELECT

    child_part, parent_part

FROM #re

ORDER BY 1, 2

 

-- 删除临时表

DROP TABLE #re, #

 

转自：http://blog.youkuaiyun.com/zjcxc/archive/2007/08/29/1763598.aspx