表自关联的问题

[Leo1734]

表结构T(C_NODE PK,P_NODE,Ser_No，Depth,Name)
其中：P_NODE为父节点的C_NODE，
Ser_No为本节点的编号，且每下降一级，编号就在父节点的基础上增加3位，
Depth表示节点深度，而且深度没有限制，目前10以内，根节点深度为0
Name本节点名称
如
T(C_NODE , P_NODE, Ser_No， Depth, name )
---------------------------------------------------
1 0 100 1 中国
2 1 100001 2 浙江
3 2 100001001 3 杭州
4 1 100002 2 广东
5 4 100002001 3 广州

现在要求查询结果如下：
First,Second,Third ……
中国 浙江 杭州 ……
中国 广东 广州 ……

SQL语句怎么写？
谢谢！

[flairsky]

select a.name,b.name,c.name,...
from (select * from T where Depth=0) a
left join (select * from T where Depth=1) b on a.C_NODE=b.P_NODE
left join (select * from T where Depth=2) c on b.C_NODE=c.P_NODE

[ChinaJiaBing]

if OBJECT_ID('tb')is not null
   drop table tb
if OBJECT_ID('c_f') is not null
   drop function c_f
go
create table tb (c_node int,p_node int,ser_no varchar(15),depth int,name varchar(10))
insert into tb select 1,0,'100',1,'中国'
     union all select 2,1,'100001',2,'浙江'
     union all select 3,2,'100001001',3,'杭州'
     union all select 4,1,'100002',2,'广东'
     union all select 5,4,'100002001',3,'广州'
go
select b.name,c.name,d.name from  
               (select * from tb where depth=1) b
               left join
               (select * from tb where depth=2) c
               on b.c_node= c.p_node left join
               (select * from tb where depth=3) d
               on c.c_node=d.p_node
               
               


name       name       name
---------- ---------- ----------
中国         浙江         杭州
中国         广东         广州

(2 行受影响)

[ljluck7687]

mark

[子陌红尘]

select a.name,b.name,c.name,...
from (select * from T where Depth=0) a
left join (select * from T where Depth=1) b on a.C_NODE=b.P_NODE
left join (select * from T where Depth=2) c on b.C_NODE=c.P_NODE
...

[htl258_Tony]

级数不多，直接用LEFT JOIN 就够用了。

[Leo1734]

如果深度未知，也就是根据实际数据来确定，该怎么查？

[子陌红尘]

[Quote=引用 3 楼 Leo1734 的回复:]
如果深度未知，也就是根据实际数据来确定，该怎么查？
[/Quote]

动态SQL：

create table T(C_NODE int,P_NODE int,Ser_No varchar(10),Depth int,name varchar(10)) 
insert into T select 1,0,'100      ',1,'中国' 
insert into T select 2,1,'100001   ',2,'浙江' 
insert into T select 3,2,'100001001',3,'杭州' 
insert into T select 4,1,'100002   ',2,'广东' 
insert into T select 5,4,'100002001',3,'广州' 
go

declare @sql1 varchar(8000),@sql2 varchar(8000),@i int,@j int
select 
    @sql1='select t1.name as [name0]',
    @sql2=' from (select * from T where Depth=1) t1',
    @i=max(Depth),
    @j=2 
from T

while @j<=@i
begin
  select @sql1=@sql1+',t'+rtrim(@j)+'.name as [name'+rtrim(@j)+']',
         @sql2=@sql2+' left join (select * from T where Depth='+rtrim(@j)+') t'+rtrim(@j)+' on t'+rtrim(@j-1)+'.C_NODE=t'+rtrim(@j)+'.P_NODE',
         @j   =@j+1
end

exec(@sql1+@sql2)
/*
name0      name2      name3      
---------- ---------- ---------- 
中国         浙江         杭州
中国         广东         广州
*/
go

drop table T
go