欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-05-03 10:33:39
刚有网友提问,只有一张表,其中有子键与父键关联,怎样根扰子键查询到父键记录的数据? Insus.NET尝试写了一个简单的例子,希望能看得懂。 CREATE TABLE [dbo].[tempTable] ( [id] INT , [parent_id] INT NULL, [itemName] NV ......

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

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