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

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

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

Source Code

下面是表关联：

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

Source Code

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

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

Source Code

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

Source Code

关于找一找教程网

本站文章仅代表作者观点，不代表本站立场，所有文章非营利性免费分享。

本站提供了软件编程、网站开发技术、服务器运维、人工智能等等IT技术文章，希望广大程序员努力学习，让我们用科技改变世界。

[同一张表中有父子键关联进行查询]http://www.zyiz.net/tech/detail-111931.html