在SQL中如何通过列值获取父级相关值

Question

我使用 SQL Server 2005 及最低版本。

我有一个如下所示的 SQL Server 表结构：

ID    Name     ParentID
-----------------------
1     Root       NULL
2     Business   1
3     Finance    1
4     Stock      3

我想编写一个查询，当用户给出输入 ID=1 时，然后在此处显示此输出：

ID    Name     ParentName
-------------------------
1     Root      -
2     Business  Root
3     Finance   Root
4     Stock     Finance    

当用户给出输入 ID=3 时，在此处显示此输出：

ID    Name     ParentName
-------------------------
3     Finance   Root
1     Root      -
4     Stock     Finance 

当用户给出输入 ID=4 时然后显示此输出：

ID    Name     ParentName
-------------------------
4     Stock     Finance    
3     Finance   Root
1     Root      -

提前致谢。如果有任何疑问请询问。谢谢大家

Answer 1

SELECT t1.ID, t1.Name, t2.Name AS ParentName FROM tableName t1
LEFT JOIN tableName t2 on t1.ID = t2.ParentID

显然将 tableName 替换为您的表格。
如果您愿意，添加t2.ID到选择列表以查看匹配。

Answer 2

以下是对 marc_s 答案的修改：

  Declare @data table
(ID bigint identity(1,1)   ,Name varchar(100),    ParentID bigint)

Insert into @data SELECT 'Root',NULL
Insert into @data SELECT 'Business',1
Insert into @data SELECT 'Finance',1
Insert into @data SELECT 'Stock',3


DECLARE @StartID INT 
SET @StartID = 3

;WITH DownHierarchy AS
(
    SELECT ID, Name, ParentID
    FROM @data
    WHERE ID = @StartID

    UNION ALL

    SELECT d.ID, d.Name, d.ParentID
    FROM @data d
    INNER JOIN DownHierarchy h ON d.ParentID = h.ID
),
UpHierarchy AS
(
    SELECT ID, Name, ParentID
    FROM @data
    WHERE ID = @StartID

    UNION ALL

    SELECT d.ID, d.Name, d.ParentID
    FROM @data d
    INNER JOIN UpHierarchy h ON d.ID = h.ParentID
)
SELECT *
FROM DownHierarchy
UNION 
SELECT *
FROM UpHierarchy

Answer 3

这两个递归 CTE（通用表表达式）将从树中的给定节点向下选择层次结构，并从该节点向上选择树回到根。由于它是 CTE，因此它可以在 SQL Server 2005 及更高版本中工作，但不幸的是，在 SQL Server 2000 中不能。

DECLARE @StartID INT 
SET @StartID = 1

;WITH DownHierarchy AS
(
    SELECT ID, Name, ParentID
    FROM dbo.YourTable
    WHERE ID = @StartID

    UNION ALL

    SELECT d.ID, d.Name, d.ParentID
    FROM dbo.YourTable d
    INNER JOIN DownHierarchy h ON d.ParentID = h.ID
),
UpHierarchy AS
(
    SELECT ID, Name, ParentID
    FROM dbo.YourTable
    WHERE ID = @StartID

    UNION ALL

    SELECT d.ID, d.Name, d.ParentID
    FROM dbo.YourTable d
    INNER JOIN UpHierarchy h ON d.ID = h.ParentID
)
SELECT *
FROM DownHierarchy
UNION 
SELECT *
FROM UpHierarchy

设置 @StartID = 1 将为您提供以下输出：

ID  Name    ParentID
 1  Root      NULL
 2  Business  1
 3  Finance   1
 4  Stock     3

设置 @StartID = 3 将为您提供以下输出：

ID  Name     ParentID
 1  Root     NULL
 3  Finance   1
 4  Stock     3

Answer 4

我有一个类似的答案 - 但构建它后我想发布它;)

declare @Data table (
    ID int not null,
    Name varchar(50) not null,
    ParentID int null
);

insert into @Data
select  1, 'Root', null
union select 2, 'Business', 1
union select 3, 'Finance', 1
union select 4, 'Stock', 3;

declare @UserInput int;
set @UserInput = 4;

with cParents as (
    select  d.ID, d.Name, d.ParentID
    from    @Data d
    where   d.ID = @UserInput
    union all
    select  d.ID, d.Name, d.ParentID
    from    cParents c
    inner join @Data d
        on  d.ID = c.ParentID
),
cChildren as (
    select  d.ID, d.Name, d.ParentID
    from    @Data d
    where   d.ID = @UserInput
    union all
    select  d.ID, d.Name, d.ParentID
    from    cChildren c
    inner join @Data d
        on  d.ParentID = c.ID
)
select  RecordType='self', d.ID, d.Name, ParentName=isnull(p.Name,'')
from    @Data d
left join @Data p
    on  p.ID = d.ParentID
where   d.ID = @UserInput

union all

select  RecordType='parents', d.ID, d.Name, ParentName=isnull(p.Name,'')
from    cParents d
left join @Data p
    on  p.ID = d.ParentID
where   d.ID <> @UserInput

union all

select  RecordType='children', d.ID, d.Name, ParentName=isnull(p.Name,'')
from    cChildren d
left join @Data p
    on  p.ID = d.ParentID
where   d.ID <> @UserInput;

@Data 代表示例数据，@UserInput 是输入变量。
我添加了 RecordType 来阐明记录部分的含义。
它在 SQL Server 2008 上进行了测试，应该可以在 2005 上运行，但不能在 2000 上运行。