使用Hierarchyid查询BOM

Question

我尝试了两种方法，首先使用物化路径和层次I查询BOM。但是结果仍然是不正确的。数量为3的ItemNumber /1/5/的结果是38.12，这个结果必须是114.36，因为乘以数量。谁能给我解释一下如何解决这个问题？

代码如下：

declare @BOMStructure as table  
(
PartNumber varchar(14)not null ,
Descript varchar(50)not null,
Qty integer not null default 0,
Price Decimal (10,2) default 0,
ItemNumber hierarchyid not null primary key 
)

INSERT @BOMStructure 
(PartNumber ,Descript ,Qty ,Price ,ItemNumber)
VALUES  ('13400201001','MAIN ASSEMBLY',1,0,'/'),
        ('00150060060005','BASIC TANK',1,0,'/1/'),
        ('11012142200503','SHELL',1,789.89,'/1/1/'),
        ('12052140503','TOP CONE',1,226.75,'/1/2/'),
        ('13052140503','BOTTOM CONE',1,226.75,'/1/3/'),
        ('140104116508','PIPE LEG',3,39.75,'/1/4/'),
        ('15004104','BALL FEET',3,0,'/1/5/'),
        ('1510413504','SLEEVE',1,18.03,'/1/5/1/'),
        ('1524809510','ADJUSTABLE BOLT',1,12.82,'/1/5/2/'),
        ('1530411604','BASE',1,7.27,'/1/5/3/')

-- GetAncestor
-- Mengupdate 
select  PartNumber, Descript,Qty,Price, (select sum (Price * Qty)
                                        from @BOMStructure where ItemNumber .IsDescendantOf (p.ItemNumber ) = 1 ) as [TotalPrice], 
        ItemNumber .ToString() as [Hierarcy], ItemNumber .GetLevel() as [Level]

from @BOMStructure as P;

Answer 1

你可以通过乘以p.qty来解决这个问题

SELECT partnumber, descript, qty, price, 
       p.qty * (SELECT Sum(price * qty) 
                FROM bomstructure 
                WHERE itemnumber.Isdescendantof(p.itemnumber) = 1
               ) as [TotalPrice], 
       itemnumber.Tostring() as [Hierarcy], 
       itemnumber.Getlevel() as [Level] 
FROM bomstructure as P; 

但是，我不能百分之百确定这是否能满足您对所有行的需求。

Here是一个SQL。