同一张表中有父子键关联进行查询

刚有网友提问，只有一张表，其中有子键与父键关联，怎样根扰子键查询到父键记录的数据？

insus.net尝试写了一个简单的例子，希望能看得懂。

 

create table [dbo].[temptable]
(
    [id] int ,
    [parent_id] int null,
    [itemname] nvarchar(40)
)
go

insert into [dbo].[temptable]
 (
     [id],
     [parent_id],
     [itemname]
 )
values
    (1,null,'a'),
    (2,null,'b'),
    (3,1,'c'),
    (4,null,'d'),
    (5,3,'e')
go

select [id],[parent_id],[itemname] from [dbo].[temptable]
go

 

下面是表关联：

 

select 
    ta.[id] as [子表id],
    tb.[id] as [父表id], 
    ta.[itemname] as [子表name],
    tb.[itemname] as [父表name] 
from [dbo].[temptable] as ta
inner join [dbo].[temptable] as tb on (ta.[parent_id] = tb.[id])
go

 

后来网友提供数据，数据如下：

 

create table [dbo].[temptable]
(
    [id] int ,
    [parent_id] int null,
    [itemname] nvarchar(40)
)
go

insert into [dbo].[temptable]
 (
     [id],
     [parent_id],
     [itemname]
 )
values
    (1,0,'广东省'),
    (2,1,'广州市'),
    (3,2,'增城区'),
    (5,3,'小池镇'),
    (8,5,'xx村'),
    (9,5,'yy村'),
    (10,5,'zz村')

go

select [id],[parent_id],[itemname] from [dbo].[temptable]
go

 

insus.net写的关联语句及查询语句：

 

select 
    ta.[id] as [a-id],
    ta.[itemname] as [a-name],
    tb.[id] as [b-id],
    tb.[itemname] as [b-name],
    tc.[id] as [c-id],
    tc.[itemname] as [c-name] ,
    td.[id] as [d-id],
    td.[itemname] as [d-name] ,
    te.[id] as [e-id],
    te.[itemname] as [e-name]
from [dbo].[temptable] as te
inner join [dbo].[temptable] as td on (te.[parent_id] = td.[id])
inner join [dbo].[temptable] as tc on (td.[parent_id] = tc.[id])
inner join [dbo].[temptable] as tb on (tc.[parent_id] = tb.[id])
inner join [dbo].[temptable] as ta on (tb.[parent_id] = ta.[id])


go