如表：
typeid parentid name
1 0 t1
2 0 t2
3 1 m
4 2 n
5 3 a
变成下表：
id name1 name2 name3
3 t1 m
4 t2 n
5 t1 m a
------解决方案--------------------
- SQL code
create table tb(typeid int,parentid int,name varchar(10))insert into tb select 1, 0, 't1'insert into tb select 2, 0, 't2'insert into tb select 3, 1, 'm'insert into tb select 4, 2, 'n'insert into tb select 5, 3, 'a'go;with cte as(select b.typeid as id,a.name as name1,b.name as name2 from tb a inner join tb b on a.typeid=b.parentid)select a.id,(case when b.name1 is null then a.name1 else b.name1 end) name1,(case when b.name1 is null then a.name2 else b.name2 end) name2,(case when b.name1 is null then '' else a.name2 end) name3from cte a left join cte b on a.name1=b.name2/*id name1 name2 name3----------- ---------- ---------- ----------3 t1 m 4 t2 n 5 t1 m a(3 行受影响)*/godrop table tb