SQLServer中Common Table Expressions_CTE 表达式

 use adventureWorks
Go
with SalesCTE(ProductID,SaleOrderID)
as
(
 select ProductID,COUNT(SaleOrderID)
 from Sales.SalesOrderDetails
 Group by ProductID
)

select * from SalesCTE

--递归调用
create database demo4
go
use demo4
go
create table CarParts
(
 CarID int not null,
 Part varchar(15),
 SubPart varchar(15),
 Qty int
)

insert into CarParts
values(1,'Body','Door',4)
insert into CarParts
values(1,'Body','Trunk Lid',1)
insert into CarParts
values(1,'Body','Car Hood',1)
insert into CarParts
values(1,'Door','Handle',1)
insert into CarParts
values(1,'Door','Lock',1)
insert into CarParts
values(1,'Door','Window',1)
insert into CarParts
values(1,'Body','Rivets',1000)
insert into CarParts
values(1,'Door','Rivets',100)
insert into CarParts
values(1,'Door','Mirror',1)
go

select * from CarParts
go

with CarPartsCTE(SubPart,Qty)
as
(
 select SubPart,Qty
 from CarParts
 where Part = 'Body'
 union all
 select CarParts.SubPart,CarPartsCTE.Qty * CarParts.Qty
 from CarPartsCTE
 inner join CarParts on CarPartsCTE.SubPart = CarParts.Part
 where CarParts.CarID = 1
) 
select SubPart,SUM(Qty) AS total from CarPartsCTE group by SubPart