SQL题目4：计算房间内物品数量

with t1 as 
(
select '房间' as product,'桌子' as fitting,2 as num from dual
union all
select '房间','椅子',3  from dual
union all
select '房间','镜子',3  from dual
union all 
select '桌子','木头块',5  from dual
union all
select '镜子','木头块',5  from dual
union all
select '桌子','螺丝',6  from dual
union all
select '桌子','螺丝帽',8  from dual
union all
select '椅子','整个木头',6  from dual
union all
select '椅子','螺丝',2  from dual
union all
select '整个木头','木头块',3  from dual
union all
select '木头块','小木头块',2  from dual
union all
select '小铁片','钉子',2  from dual
union all
select '小铁片','铝片',3  from dual
union all
select '螺丝','大铁片',10  from dual
union all
select '螺丝','中铁片',10  from dual
union all
select '螺丝','小铁片',10  from dual
)
复制

cte(product,fitting,num) as 
(
select product,fitting ,num  from t1  where product = '房间'
union all
select t.product,t.fitting,t.num*c.num as num
from t1 t inner join cte c on c.fitting = t.product 
)
select fitting,sum(num) from cte group by fitting
复制