SQL Server 2005 Beta 2 Transact-SQL 增强功能(二)

以下为结果集：

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

锚定成员从 BOM 中返回 A 直接包含的所有项。对于 CTE 的上一个迭代返回的每个被包含的项，递归成员都通过将 BOM 与 BOMCTE 联接来返回它包含的项。从逻辑上讲，（未必是输出中的顺序）首先返回 (A, B)、(A, C)、(A, D)，然后返回 (B, F)、(B, G)、(C, B)、(C, E);最后返回 (B, F)、(B, G)、(E, J)。请注意，BOM 中的大多数请求都不需要您在最后结果中显示某个项一次以上。如果您只是希望显示爆炸中涉及到“哪些”项，则可以使用 DISTINCT 子句消除重复项：

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

以下为结果集：

containsid
----------
B
C
D
E
F
G
J

为了帮助您了解部分爆炸的过程，将它的中间结果直观地表示为树，其中所有项都被展开到它们的被包含的项。图 3 显示了通过使部分 A 和 H 爆炸而形成的树以及项数量。

图 3. 部分爆炸
将原始请求向前推进一步，您可能对获得每个项的累积数量而不是获得项本身更感兴趣。例如，A 包含 2 个单位的 C。C 包含 3 个单位的 E。E 包含 1 个单位的 J。A 所需的 J 的单位总数是沿从 A 通向 J 的路径的数量的乘积：2*3*1 = 6。图 4 显示了在聚合项之前构成 A 的每个项的累积数量。

图 4. 部分爆炸 — 计算得到的数量
以下 CTE 计算数量的累积乘积：

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

以下为结果集：

itemid containsid qty cumulativeqty
------ ---------- ----------- -------------
A B 2 2
A C 2 2
A D 2 2
C B 2 4
C E 3 6
E J 1 6
B F 1 4
B G 3 12
B F 1 2
B G 3 6

该 CTE 将 cumulativeqty 列添加到上一个 CTE 中。锚定成员将被包含的项的数量作为 cumulativeqty 返回。对于下一个级别的每个被包含的项，递归成员都将它的数量乘以它的包含项的累积数量。请注意，从多个路径到达的项在结果中出现多次，每一次都带有对应于每个路径的累积数量。这样的输出本身不是很有意义，但是它可以帮助您了解通向每个项只出现一次的最终结果的中间步骤。要获得 A 中的每个项的总数量，请让外部查询按照 containsid 对结果进行分组：

WITH BOMCTE(itemid, containsid, qty, cumulativeqty)
AS
(

SELECT *, qty
FROM BOM
WHERE itemid = ''A''
UNION ALL
SELECT BOM.*, BOM.qty * BOMCTE.cumulativeqty
FROM BOM
JOIN BOMCTE
ON BOM.itemid = BOMCTE.containsid
)
SELECT containsid AS itemid, SUM(cumulativeqty) AS totalqty
FROM BOMCTE
GROUP BY containsid

以下为结果集：

itemid totalqty
------ -----------
B 6
C 2
D 2
E 6
F 6
G 18
J 6

PIVOT 和 UNPIVOT

PIVOT 和 UNPIVOT 是可以在查询的 FROM 子句中指定的新的关系运算符。它们对一个输入表值表达式执行某种操作，并且产生一个输出表作为结果。PIVOT 运算符将行旋转为列，并且可能同时执行聚合。它基于给定的枢轴列扩大输入表表达式，并生成一个带有与枢轴列中的每个唯一值相对应的列的输出表。UNPIVOT 运算符执行与 PIVOT 运算符相反的操作;它将列旋转为行。它基于枢轴列收缩输入表表达式。

PIVOT

PIVOT 运算符可用来处理开放架构方案以及生成交叉分析报表。

在开放架构