层次结构显示 Sybase 表数据 [英] Hierarchy display Sybase table data

问题描述

EID     PID     Name

1       NULL    A
2       1       B
3       2       C
4       3       D
5       1       E
6       1       F
7       1       G
8       6       H

以上信息显示的是表的实际数据，我想将表中的数据显示如下.

The above info shows actual data of a table, I want to display the data in that table as below .

即使用 EID 和 PID 分层显示数据.

i.e. Hierarchy display of data using EID and PID .

这里PID表示ParentID，EID是EntityID，使用ParentID我们需要得到如下的Hierarchy.

Here PID means ParentID, and EID is EntityID, Using ParentID we need to get the Hierarchy as below.

Level1  Level2 Level3  Level4

 A      NULL    NULL    NULL
 A       B      NULL    NULL
 A       B       C      NULL
 A       B       C       D
 A       E      NULL    NULL
 A       F      NULL    NULL
 A       F       H      NULL
 A       G      NULL    NULL 

推荐答案

Disclaimer: it's made in (MS-SQL)

Disclaimer: it's a example made in (MS-SQL)

@Gordon Linoff 的回答几乎做到了，只是忘记了基本情况(其中 Pid 为空)

@Gordon Linoff answer almost did it, just forgot the base case (where Pid is null)

只需按照这种左连接+联合的模式来覆盖固定数量级别的基本情况，对于动态数量的操作系统级别，您需要使用递归

just follow this pattern of left joins + unions to cover the base cases for a fixed amount of levels, for a dynamic number os levels you ill need to use recursion

declare @Tree as
table (
    Eid int not null
   ,Pid int null
   ,Name char(1) not null
)

insert into @Tree
values
 (1, NULL, 'A')
,(2,    1, 'B')
,(3,    2, 'C')
,(4,    3, 'D')
,(5,    1, 'E')
,(6,    1, 'F')
,(7,    1, 'G')
,(8,    6, 'H')

(
select t1.Name as [Level 1], null as [Level 2], null as [Level 3], null as [Level 4]
from @Tree t1
where t1.Pid is null
union
select t1.Name as [Level 1], t2.Name as [Level 2], null as [Level 3], null as [Level 4]
from @Tree t1
left join @Tree t2 on t2.Pid = t1.Eid
where t1.Pid is null
union
select t1.Name as [Level 1], t2.Name as [Level 2], t3.Name as [Level 3], null as [Level 4]
from @Tree t1
left join @Tree t2 on t2.Pid = t1.Eid
left join @Tree t3 on t3.Pid = t2.Eid
where t1.Pid is null
union
select t1.Name as [Level 1], t2.Name as [Level 2], t3.Name as [Level 3], t4.Name as [Level 4]
from @Tree t1
left join @Tree t2 on t2.Pid = t1.Eid
left join @Tree t3 on t3.Pid = t2.Eid
left join @Tree t4 on t4.Pid = t3.Eid
where t1.Pid is null
) order by [Level 1], [Level 2], [Level 3], [Level 4]

和使用右连接而不是左连接的相同查询

and the same query using right join instead of left joins

declare @Tree as
table (
    Eid int not null
   ,Pid int null
   ,Name char(1) not null
)

insert into @Tree
values
 (1, NULL, 'A')
,(2,    1, 'B')
,(3,    2, 'C')
,(4,    3, 'D')
,(5,    1, 'E')
,(6,    1, 'F')
,(7,    1, 'G')
,(8,    6, 'H')

(
select t1.Name as [Level 1], null as [Level 2], null as [Level 3], null as [Level 4]
from @Tree t1
where t1.Pid is null
union
select t1.Name as [Level 1], t2.Name as [Level 2], null as [Level 3], null as [Level 4]
from @Tree t2
right join @Tree t1 on t2.Pid = t1.Eid
where t1.Pid is null
union
select t1.Name as [Level 1], t2.Name as [Level 2], t3.Name as [Level 3], null as [Level 4]
from @Tree t3
right join @Tree t2 on t3.Pid = t2.Eid
right join @Tree t1 on t2.Pid = t1.Eid
where t1.Pid is null
union
select t1.Name as [Level 1], t2.Name as [Level 2], t3.Name as [Level 3], t4.Name as [Level 4]
from @Tree t4
right join @Tree t3 on t4.Pid = t3.Eid
right join @Tree t2 on t3.Pid = t2.Eid
right join @Tree t1 on t2.Pid = t1.Eid
where t1.Pid is null
) order by [Level 1], [Level 2], [Level 3], [Level 4]

这篇关于层次结构显示 Sybase 表数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！