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

核心提示： 锚定成员从 BOM 中返回 A 直接包含的所有项，对于 CTE 的上一个迭代返回的每个被包含的项，SQL Server 2005 Beta 2 Transact-SQL 增强功能 1(11)，递归成员都通过将 BOM 与 BOMCTE 联接来返回它包含的项，从逻辑上讲，例如，size 属性存储整数属性值，（未必是输出

锚定成员从 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 BOMCTEAS(SELECT *FROM BOMWHERE itemid = 'A'UNION ALLSELECT BOM.*FROM BOMJOIN BOMCTEON BOM.itemid = BOMCTE.containsid)SELECT DISTINCT containsid FROM BOMCTE

以下为结果集：

containsid----------BCDEFGJ

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

(WINDOWS平台上强大的数据库平台) 2005 Beta 2 Transact-SQL 增强功能 1（图三）" />

图 3. 部分爆炸

 

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

(WINDOWS平台上强大的数据库平台) 2005 Beta 2 Transact-SQL 增强功能 1（图四）" />

图 4. 部分爆炸 — 计算得到的数量

 

以下 CTE 计算数量的累积乘积：

WITH BOMCTE(itemid, containsid, qty, cumulativeqty)AS(SELECT *, qtyFROM BOMWHERE itemid = 'A'UNION ALLSELECT BOM.*, BOM.qty * BOMCTE.cumulativeqtyFROM BOMJOIN BOMCTEON BOM.itemid = BOMCTE.containsid)SELECT * FROM BOMCTE

以下为结果集：

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

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

WITH BOMCTE(itemid, containsid, qty, cumulativeqty)AS(SELECT *, qtyFROM BOMWHERE itemid = 'A'UNION ALLSELECT BOM.*, BOM.qty * BOMCTE.cumulativeqtyFROM BOMJOIN BOMCTEON BOM.itemid = BOMCTE.containsid)SELECT containsid AS itemid, SUM(cumulativeqty) AS totalqtyFROM BOMCTEGROUP BY containsid

以下为结果集：

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

PIVOT 和 UNPIVOT

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

PIVOT

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

在开放架构方案中，您需要用事先不知道或因实体类型而异的属性集来维护实体。应用程序的用户动态定义这些属性。您将属性拆分到不同的行中，并且只为每个实体实例存储相关的属性，而不是在表中预定义很多列并存储很多空值。

PIVOT 使您可以为开放架构和其他需要将行旋转为列的方案生成交叉分析报表，并且可能同时计算聚合并且以有用的形式呈现数据。

开放架构方案的一个示例是跟踪可供拍卖的项目的数据库。某些属性与所有拍卖项目有关，例如，项目类型、项目的制造日期以及它的初始价格。只有与所有项目有关的属性被存储在 AuctionItems 表中：

CREATE TABLE AuctionItems(itemid       INT          NOT NULL Prima(最完善的虚拟主机管理系统)RY KEY NONCLUSTERED,itemtype     NVARCHAR(30) NOT NULL,whenmade     INT          NOT NULL,initialprice MONEY        NOT NULL,/* other columns */)CREATE UNIQUE CLUSTERED INDEX idx_uc_itemtype_itemidON AuctionItems(itemtype, itemid)INSERT INTO AuctionItems VALUES(1, N'Wine',     1822,      3000)INSERT INTO AuctionItems VALUES(2, N'Wine',     1807,       500)INSERT INTO AuctionItems VALUES(3, N'Chair',    1753,    800000)INSERT INTO AuctionItems VALUES(4, N'Ring',     -501,   1000000)INSERT INTO AuctionItems VALUES(5, N'Painting', 1873,   8000000)INSERT INTO AuctionItems VALUES(6, N'Painting', 1889,   8000000)

其他属性特定于项目类型，并且不同类型的新项目被不断地添加。这样的属性可以存储在不同的 ItemAttributes 表中，其中每个项属性都存储在不同的行中。每个行都包含项目 ID、属性名称和属性值：

CREATE TABLE ItemAttributes(itemid    INT          NOT NULL REFERENCES AuctionItems,attribute NVARCHAR(30) NOT NULL,value     SQL_VARIANT  NOT NULL,Prima(最完善的虚拟主机管理系统)RY KEY (itemid, attribute))INSERT INTO ItemAttributesVALUES(1, N'manufacturer', CAST(N'ABC'              AS NVARCHAR(30)))INSERT INTO ItemAttributesVALUES(1, N'type',         CAST(N'Pinot Noir'       AS NVARCHAR(15)))INSERT INTO ItemAttributesVALUES(1, N'color',        CAST(N'Red'              AS NVARCHAR(15)))INSERT INTO ItemAttributesVALUES(2, N'manufacturer', CAST(N'XYZ'              AS NVARCHAR(30)))INSERT INTO ItemAttributesVALUES(2, N'type',         CAST(N'Porto'            AS NVARCHAR(15)))INSERT INTO ItemAttributesVALUES(2, N'color',        CAST(N'Red'              AS NVARCHAR(15)))INSERT INTO ItemAttributesVALUES(3, N'material',     CAST(N'Wood'             AS NVARCHAR(15)))INSERT INTO ItemAttributesVALUES(3, N'padding',      CAST(N'Silk'             AS NVARCHAR(15)))INSERT INTO ItemAttributesVALUES(4, N'material',     CAST(N'Gold'             AS NVARCHAR(15)))INSERT INTO ItemAttributesVALUES(4, N'inscription',  CAST(N'One ring ...'     AS NVARCHAR(50)))INSERT INTO ItemAttributesVALUES(4, N'size',         CAST(10                  AS INT))INSERT INTO ItemAttributesVALUES(5, N'artist',       CAST(N'Claude Monet'     AS NVARCHAR(30)))INSERT INTO ItemAttributesVALUES(5, N'name',         CAST(N'Field of Poppies' AS NVARCHAR(30)))INSERT INTO ItemAttributesVALUES(5, N'type',         CAST(N'Oil'              AS NVARCHAR(30)))INSERT INTO ItemAttributesVALUES(5, N'height',       CAST(19.625              AS NUMERIC(9,3)))INSERT INTO ItemAttributesVALUES(5, N'width',        CAST(25.625              AS NUMERIC(9,3)))INSERT INTO ItemAttributesVALUES(6, N'artist',       CAST(N'Vincent Van Gogh' AS NVARCHAR(30)))INSERT INTO ItemAttributesVALUES(6, N'name',         CAST(N'The Starry Night' AS NVARCHAR(30)))INSERT INTO ItemAttributesVALUES(6, N'type',         CAST(N'Oil'              AS NVARCHAR(30)))INSERT INTO ItemAttributesVALUES(6, N'height',       CAST(28.75               AS NUMERIC(9,3)))INSERT INTO ItemAttributesVALUES(6, N'width',        CAST(36.25               AS NUMERIC(9,3)))

请注意，sql_variant 数据类型被用于 value 列，因为不同的属性值可能具有不同的数据类型。例如，size 属性存储整数属性值，而 name 属性存储字符串属性值。