原来SQL也可以递归

create table f0307(
id int,
productname varchar(64),
parentid int
)

insert into f0307 values(1,'汽车',null)
insert into f0307 values(2,'车身',1)
insert into f0307 values(3,'发动机',1)
insert into f0307 values(4,'车门',2)
insert into f0307 values(5,'驾驶舱',2)
insert into f0307 values(6,'行李舱',2)
insert into f0307 values(7,'气缸',3)
insert into f0307 values(8,'活塞',3)
insert into f0307 values(9,'火花塞1',8)
with cte as
(
select id,parentid,productname,0 ctelevel,cast(id as varchar) orderid from f0307 where id=1
union all
select a.id,a.parentid,a.productname,b.ctelevel+1,cast(b.orderid + '->' + ltrim(a.id) as varchar)
from f0307 a
join cte b on b.id=a.parentid
)


select id,parentid,
right('            ',4*ctelevel) + productname productname ,orderid
from cte order by orderid