у меня есть таблица, в которой некоторые части автомобиля связаны иерархически, и у меня также есть стоимость изготовления этих частей в каждой из строк. Это упрощение таблицы:

parentId  Id description qty manufacturingCost costDescripcion
-------- --- ----------- --- ----------------- ---------------
NULL      1  Car          1  100               Assembly the car
NULL      2  Motorcycle   1  100               Assembly the motrocycle
 1       11  Wheel        4   20               Assembly the wheel
11      111  Rim          1   50               Manufacture the rim
11      112  Tire         1   60               Manufacture the tire
 1       12  Door+Window  4   30               Assembly the door and the window
12      121  Door         1   30               Manufacture the door
12      122  Window       2   10               Manufacture the window
 2       11  Wheel        2   15               Assembly the wheel

Мне нужно, чтобы все генеалогическое древо начиналось с 'Автомобиль' и показывая общее количество и общие затраты для каждого филиала. Лучше объяснить: у машины есть 4 колеса, и у каждого колеса есть 1 обод и 1 шина, поэтому я должен получить 1 автомобиль, 4 колеса, 4 шины, 4 обода. Немного сложнее по стоимости: сборка автомобиля стоит 100 $, но я должен добавить к этой стоимости сборку 4-х колес (4х20) и стоимость изготовления 4-х дисков (4х50) и 4-х шин (4х60), и то же самое для дверей и окон.

Это ожидаемый результат:

parentId  Id description qty manufacturingCost   recLevel
-------- --- ----------- --- -----------------   ---------------
NULL       1  Car          1 940 (100+4*130+4*80) 0
 1        11  Wheel        4 130 (20+50+60)       1
 1        12  Door+Window  4 80  (30+30+2*10)     1
12       121  Door         4 30                   2
12       122  Window       8 10                   2
11       111  Rim          4 50                   2
11       112  Tire         4 60                   2

Я легко могу достичь этого с помощью рекурсивной функции или хранимой процедуры, но это очень медленно с более сложными структурами, поэтому я пытаюсь сделать это с помощью выражений Common Table. Но я неНе могу найти способ суммировать затраты. Я использую рекурсивный CTE, начиная с верхнего уровня и спускаясь вниз, и получаю сумму величин, но мне нужно перейти изнутри наружу в структуре для суммирования затрат, как я могу это сделать?

Это код для создания таблицы:

CREATE TABLE #Costs 
(
  parentId int, 
  Id int, 
  description varchar(50),
  qty int, 
  manufacturingCost int,
  costDescripcion varchar(150)
)

INSERT INTO #Costs VALUES (NULL , 1, 'Car', 1, 100, 'Assembly the car')
INSERT INTO #Costs VALUES (NULL , 2, 'Motorcycle', 1, 100, 'Assembly the motrocycle')
INSERT INTO #Costs VALUES (1 , 11, 'Wheel', 4, 20, 'Assembly the wheel')
INSERT INTO #Costs VALUES (11 , 111, 'Rim', 1, 50, 'Manufacture the rim')
INSERT INTO #Costs VALUES (11 , 112, 'Tire', 1, 60, 'Manufacture the tire')
INSERT INTO #Costs VALUES (1 , 12, 'Door+Window', 4, 30, 'Assembly the door and the window')
INSERT INTO #Costs VALUES (12 , 121, 'Door', 1, 30, 'Manufacture the door')
INSERT INTO #Costs VALUES (12 , 122, 'Window', 2, 10, 'Manufacture the window')
INSERT INTO #Costs VALUES (2 , 11, 'Wheel', 2, 15, 'Assembly the wheel')

И это тот CTE, который я написал:

with CTE(parentId, id, description, totalQty, manufacturingCost, recLevel)
as
(
  select c.parentId, c.id, c.description, c.qty, c.manufacturingCost, 0
  from #Costs c
  where c.id = 1

  union all

  select c.parentId, c.id, c.description, c.qty * ct.totalQty, c.manufacturingCost, ct.recLevel + 1
  from #Costs c
  inner join CTE ct on ct.id = c.parentId 
)
select * from CTE

И вот результат, который я получаю, что, как вы видите, не является ожидаемым (затраты не добавляются):

parentId  Id description qty manufacturingCost recLevel
-------- --- ----------- --- ----------------- ---------------
NULL       1  Car          1 100                0
 1        11  Wheel        4 20                 1
 1        12  Door+Window  4 30                 1
12       121  Door         4 30                 2
12       122  Window       8 10                 2
11       111  Rim          4 50                 2
11       112  Tire         4 60                 2

Можно ли делать то, что я хочу, используя CTE? Если так, как я могу это сделать?

Большое спасибо,

Antuan