SQL Server 2005 Beta 2 Transact-SQL 增强功能 1

来源：互连网作者：出处：巧巧读书 2006-02-24 进入讨论组

多父节点环境：材料清单

在上一节中，使用 CTE 处理层次结构，其中，树中的每个节点都只有单个父节点。更复杂的关系情况是这样的图形，其中每个节点都可能具有一个以上的父节点。本节描述材料清单 (BOM) 方案中 CTE 的用法。BOM 是一个非循环有向图形，这意味着每个节点都可以具有一个以上的父节点；节点不能是其本身的直接或间接父节点；两个节点之间的关系不是双重的（例如，A 包含 C，但是 C 不能包含 A）。图 2 显示了 BOM 方案中的项之间的关系。

SQL Server 2005 Beta 2 Transact-SQL 增强功能 1（图二）

图 2. 多父节点环境

项 A 包含项 D、B 和 C；项 C 包含 B 和 E；项 B 包含在项 A 和 C 中；等等。以下代码创建了 Items 和 BOM 表，并且用示例数据填充它们：

CREATE TABLE Items
(
itemid   VARCHAR(5)  NOT NULL PRIMARY KEY,
itemname VARCHAR(25) NOT NULL,
/* other columns, e.g., unit_price, measurement_unit */
)
CREATE TABLE BOM
(
itemid     VARCHAR(5) NOT NULL REFERENCES Items,
containsid VARCHAR(5) NOT NULL REFERENCES Items,
qty        INT        NOT NULL
/* other columns, e.g., quantity */
PRIMARY KEY(itemid, containsid),
CHECK (itemid <> containsid)
)
SET NOCOUNT ON
INSERT INTO Items(itemid, itemname) VALUES('A', 'Item A')
INSERT INTO Items(itemid, itemname) VALUES('B', 'Item B')
INSERT INTO Items(itemid, itemname) VALUES('C', 'Item C')
INSERT INTO Items(itemid, itemname) VALUES('D', 'Item D')
INSERT INTO Items(itemid, itemname) VALUES('E', 'Item E')
INSERT INTO Items(itemid, itemname) VALUES('F', 'Item F')
INSERT INTO Items(itemid, itemname) VALUES('G', 'Item G')
INSERT INTO Items(itemid, itemname) VALUES('H', 'Item H')
INSERT INTO Items(itemid, itemname) VALUES('I', 'Item I')
INSERT INTO Items(itemid, itemname) VALUES('J', 'Item J')
INSERT INTO Items(itemid, itemname) VALUES('K', 'Item K')
INSERT INTO BOM(itemid, containsid, qty) VALUES('E', 'J', 1)
INSERT INTO BOM(itemid, containsid, qty) VALUES('C', 'E', 3)
INSERT INTO BOM(itemid, containsid, qty) VALUES('A', 'C', 2)
INSERT INTO BOM(itemid, containsid, qty) VALUES('H', 'C', 4)
INSERT INTO BOM(itemid, containsid, qty) VALUES('C', 'B', 2)
INSERT INTO BOM(itemid, containsid, qty) VALUES('B', 'F', 1)
INSERT INTO BOM(itemid, containsid, qty) VALUES('B', 'G', 3)
INSERT INTO BOM(itemid, containsid, qty) VALUES('A', 'B', 2)
INSERT INTO BOM(itemid, containsid, qty) VALUES('A', 'D', 2)
INSERT INTO BOM(itemid, containsid, qty) VALUES('H', 'I', 1)

Items 表包含与每个项对应的行。BOM 表包含该图形中节点之间的关系。每个关系都由父项 ID (itemid)、子项 ID (containsid) 以及 itemid (qty) 中 containsid 的数量组成。

BOM 方案中的常见请求是使项“爆炸”：即遍历图形 — 从给定项开始并返回它直接或间接包含的所有项。这听起来可能很熟悉，因为它类似于“雇员组织结构图”一节中介绍的从树中返回子树的操作。但是，在有向图形中，该请求在概念上更为复杂一些，因为可以从多个不同的包含项通过不同的路径到达一个被包含的项。例如，假设您要使项 A 爆炸。请注意，有两个不同的路径从它通向项 B：A->B 和 A->C->B。这意味着项 B 会被到达两次，继而意味着 B 包含的所有项（F 和 G）会被到达两次。幸运的是，通过 CTE 实现这样的请求就像实现从树中获得子树的请求一样简单：

WITH BOMCTE
AS
(
SELECT *
FROM BOM
WHERE itemid = 'A'
UNION ALL
SELECT BOM.*
FROM BOM
JOIN BOMCTE
ON BOM.itemid = BOMCTE.containsid
)
SELECT * FROM BOMCTE

以下为结果集：

itemid containsid qty
------ ---------- -----------
A      B          2
A      C          2
A      D          2
C      B          2
C      E          3
E      J          1
B      F          1
B      G          3
B      F          1
B      G          3

巧巧读书:http://www.qqread.com/sqlserver/f062384005.html