问题描述
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 表数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!