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

r囧r小猫 2021-11-22 09:12 287阅读 0赞

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

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

[![28036-20190517220529441-2037186736.png][]][28036-20190517220529441-2037186736.png]

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

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

下面是表关联：

[![28036-20190517220811098-1454202352.png][]][28036-20190517220811098-1454202352.png]

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

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

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

[![28036-20190517223743324-148170671.png][]][28036-20190517223743324-148170671.png]

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

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写的关联语句及查询语句：

[![28036-20190517224058281-1439994268.png][]][28036-20190517224058281-1439994268.png]

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

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

转载于:https://www.cnblogs.com/insus/p/10883964.html

[28036-20190517220529441-2037186736.png]: /images/20211122/1fb0a07b7c094f15983cec2082f3852f.png
[ContractedBlock.gif]: https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif
[ExpandedBlockStart.gif]: /images/20211122/f2f96c9adba0484bbf639b33814db2f2.png
[28036-20190517220811098-1454202352.png]: /images/20211122/5164889d9751442984081cbcf85e15a1.png
[28036-20190517223743324-148170671.png]: /images/20211122/8bbbc9f957f54e41b8af24283c1dbbc6.png
[28036-20190517224058281-1439994268.png]: /images/20211122/04e80101bda840f1b26234c46db32be2.png