如何仅查询前三个参考ID，以及如何查询直到仅基于参考ID的三级组以下 [英] How To Query Top Three Reference ID only and How I can query until 3 Level Group below only Based Reference ID

问题描述

我下面有这样的表，其中保存了所有带有参考ID的数据：-

I have table like this below where it save all data with reference id :-

ID  Name    RefID
A   AAAA    null
B   BBBB    A
C   CCCC    B
D   DDDD    C
E   EEEE    D
F   FFFF    E

首先，我需要查询一些可以扫描的查询，这些查询仅是前三个参考ID级别并显示出来。

Firstly I need some query where I can scan which are the top three reference id level only and show up.

---例如：当D添加到C以下时--

--- Example: When D added below C ---

GLevel  ID  RefID   No
Start   A       (3)
Level(1)    B   (A) (2)     
Level(2)    C   (B) (1)
Level(3)   D    (C)

结果（从下到上）：A，B和C是D
结果的前三个参考ID（自上而下）：A将B，C和D用作基于子对象的refid

Result (Down To Top): A,B and C are top three reference id for D Result (Top to Down): A have B,C and D as child based refid

当E在D下方输入时，A对E无效。我需要显示E的前三个参考ID。

When E has enter below D, then A are not valid reference id for E. I need to show top three reference id for E.

---例如：当E添加到D以下时---

--- Example: When E added below D ---

GLevel  Id  RefID   No
Start   A   
Level(1)    B   (A) (3)
Level(2)    C   (B) (2)
Level(3)   D    (C) (1)
Level(4)   E    (D)

结果（向下）：D，C B是E
结果的前三个参考ID（从上到下）：B具有C，D和E作为基于子项的refid

Result (Down To Top): D,C and B are top three reference id for E Result (Top to Down): B have C,D and E as child based refid

作为最终结果我需要重新排列并存储每个ID的计算视图，直到组级别3如下所示：-

As a final result I need to rearrange and store my compute view for each ID till Group Level 3 only like below:-

id  GroupLevel  RefID   
A   Level(1)        B   
A   Level(2)        C
A   Level(3)        D
B   Level(1)        C
B   Level(2)        D
B   Level(3)        E
C   Level(1)        D
C   Level(2)        E
D   Level(1)        E
E   null

谁遇到了这个问题，请帮我解决。无论如何，请提前感谢您阅读此问题。

Who have cross this problem please help me how to solve it. Anyway thanks on advance for reading this question.

推荐答案

Postgreql版本（||代替 +进行串联和带引号的别名）：

Postgreql version (|| instead of '+' for concatenation and quoted level alias):

select ID, 'Level(' || cast(level as varchar(255)) || ')', RefID from (
    select t0.ID, 
    case level.level
        when 1 then t1.ID
        when 2 then t2.ID
        when 3 then t3.ID 
    end RefID,
    level.level
    from Test t0
    left outer join Test t1 on t1.RefID = t0.ID
    left outer join Test t2 on t2.RefID = t1.ID
    left outer join Test t3 on t3.RefID = t2.ID
    cross join (
        select 1 "level" union all
        select 2 union all
        select 3
    ) level
) t
where t.RefID is not null

这篇关于如何仅查询前三个参考ID，以及如何查询直到仅基于参考ID的三级组以下的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！