使用临时表 + 循环展 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' <place w:st="on"><span style="COLOR: blue">UNION</span></place> ALL

SELECT 'B','C' <place w:st="on"><span style="COLOR: blue">UNION</span></place> ALL

SELECT 'C','D' <place w:st="on"><span style="COLOR: blue">UNION</span></place> ALL

SELECT 'D','E' <place w:st="on"><span style="COLOR: blue">UNION</span></place> ALL

SELECT 'D','F' <place w:st="on"><span style="COLOR: blue">UNION</span></place> 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, #