我有以下两张table：

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 (710)
4 31 42 100 Screw 142 3 71000(10070)
5 31 44 56 Screw 144 3 3920 (70*56)
6 44 57 10 iron 4 39200