sql - 如何解决公共表表达式的递归成员有多个递归引用？

Question

我有以下两个表：

IF OBJECT_ID('tempdb.dbo.##BillsDetails', 'U') IS NOT NULL
  DROP TABLE ##BillsDetails; 
  IF OBJECT_ID('tempdb.dbo.##Material', 'U') IS NOT NULL
  DROP TABLE ##Material; 
CREATE TABLE ##BillsDetails
( ID int PRIMARY KEY NOT NULL,
 ParentID varchar(50) NULL,
 ChildID  varchar(50) NULL,
 Quantity  float NULL)
   INSERT INTO ##BillsDetails (ID, ParentID, ChildID,Quantity) VALUES (1, 68,34, 10)
   INSERT INTO ##BillsDetails (ID, ParentID, ChildID,Quantity) VALUES (2, 68,86, 13)
   INSERT INTO ##BillsDetails (ID, ParentID, ChildID,Quantity) VALUES (3, 34,31, 7)
   INSERT INTO ##BillsDetails (ID, ParentID, ChildID,Quantity) VALUES (4, 31,42, 100)
   INSERT INTO ##BillsDetails (ID, ParentID, ChildID,Quantity) VALUES (5, 31,44, 56)
   INSERT INTO ##BillsDetails (ID, ParentID, ChildID,Quantity) VALUES (6, 44,57, 10)
   CREATE TABLE ##Material
( MaterialID int PRIMARY KEY NOT NULL,
MaterialName varchar(500) NULL)
INSERT INTO ##Material (MaterialID, MaterialName) VALUES ( 68,'Closet')
INSERT INTO ##Material (MaterialID, MaterialName) VALUES ( 34,'Closet Door')
INSERT INTO ##Material (MaterialID, MaterialName) VALUES ( 86,'Shelf')
INSERT INTO ##Material (MaterialID, MaterialName) VALUES ( 31,'Rod')
INSERT INTO ##Material (MaterialID, MaterialName) VALUES ( 42,'Screw 142')
INSERT INTO ##Material (MaterialID, MaterialName) VALUES ( 44,'Screw 144')
INSERT INTO ##Material (MaterialID, MaterialName) VALUES ( 57,'iron')

##BillsDetails包含每种材料，其组成材料列表（ChildID）及其必要数量，我进行了以下递归查询以获取给定材料的所有子项及其子项，并获取每种材料的总数量，物料的总数量=它自己的数量*它的父级的总数量。

Declare @IDmaterial int
set @IDmaterial= 68;
with JoinCTE AS
(
select det.ID, det.ParentID, det.ChildID, det.Quantity, M.MaterialName, 1 as [level], det.Quantity as TotalQuantity
from ##BillsDetails det
Left Join ##Material  M on det.ChildID= M.MaterialID
),
BillsCTE as(
select ID, ParentID, ChildID, Quantity, MaterialName, 1 as [level], TotalQuantity
From JoinCTE
where ParentID=@IDmaterial
UNION ALL
Select A.ID, A.ParentID, A.ChildID, A.Quantity, A.MaterialName, BillsCTE.[level]+1, 
(select A.Quantity*B.TotalQuantity from BillsCTE B where A.ChildID= B.ParentID)
  as TotalQuantity
from JoinCTE A 
inner join BillsCTE  on A.ParentID=BillsCTE.ChildID
)
select * from BillsCTE

这个子查询

(select A.Quantity*B.TotalQuantity from BillsCTE B where A.ChildID= B.ParentID)

返回以下错误

公用表表达式的递归成员具有多个递归引用

如何在不参考 BillsCTE 的情况下计算总数量？

编辑：预期输出：

ID ParentID ChildID Quantity MaterialName level TotalQuantity
1  68       34      10       Closet Door  1     10 (level 1 child=>TotalQ=Q)
2  68       86      13       Shelf        1     13
3  34       31      7        Rod          2     70 (7*10)
4  31       42      100      Screw 142    3     71000(100*70)
5  31       44      56       Screw 144    3     3920 (70*56)
6  44       57      10       iron         4     39200

标签： sqlsql-serverrecursioncommon-table-expression