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

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

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