Oracle/SQL:深度，层次关系

Question

关于这件事我需要你的帮助;

Fiedl1_Id        Fiedl1      Fiedl2_Id      Fiedl2
2470             199T        2348              949T 
2470             199T        2349              699T 
2470             199T        2356              649T 
2470             199T        2379              399T 
2470             199T        2383              299T 
2470             199T        2470              199T 

我想在我的sql查询代码中添加一个查询的输出，一个新的字段深度，其原理如下:

Fiedl1_Id   Fiedl1  Fiedl2_Id   Fiedl2  Depth
2470        199T    2348        949T    1
2470        199T    2348        949T    2
2470        199T    2348        949T    3
2470        199T    2348        949T    4
2470        199T    2348        949T    5
2470        199T    2349        699T    1
2470        199T    2349        699T    2
2470        199T    2349        699T    3
2470        199T    2349        699T    4
2470        199T    2356        649T    1
2470        199T    2356        649T    2
2470        199T    2356        649T    3
2470        199T    2379        399T    1
2470        199T    2379        399T    2
2470        199T    2383        299T    1
2470        199T    2470        199T    0

我有6个记录，包括一个记录，有Fiedl1_Id = Fiedl2_Id(2470)这个必须有一个深度= 0，对于其他人;Fiedl2(949T)的第一个值有5个深度，(699T)有4个深度，依次递减。

Answer 1

这将给出给定输入所需的输出:

WITH t (fiedl1_id,fiedl1,fiedl2_id,fiedl2)
AS
(
SELECT '2470','199T','2348','949T' FROM DUAL UNION ALL
SELECT '2470','199T','2349','699T' FROM DUAL UNION ALL
SELECT '2470','199T','2356','649T' FROM DUAL UNION ALL
SELECT '2470','199T','2379','399T' FROM DUAL UNION ALL
SELECT '2470','199T','2383','299T' FROM DUAL UNION ALL
SELECT '2470','199T','2470','199T' FROM DUAL
), t_rn (fiedl1_id,fiedl1,fiedl2_id,fiedl2,rn) 
AS
(
SELECT fiedl1_id,fiedl1,fiedl2_id,fiedl2,ROW_NUMBER() OVER(ORDER BY 1) FROM t ORDER BY fiedl2 ASC
), t_depth (fiedl1_id,fiedl1,fiedl2_id,fiedl2,fdepth)
AS
(
SELECT fiedl1_id,fiedl1,fiedl2_id,fiedl2,rn - 1 FROM t_rn 
UNION ALL 
SELECT fiedl1_id,fiedl1,fiedl2_id,fiedl2,fdepth - 1 FROM t_depth WHERE fdepth - 1 > 0
)
SELECT * FROM t_depth order by fiedl2 DESC, fdepth ASC;
FIED FIED FIED FIED     FDEPTH
---- ---- ---- ---- ----------
2470 199T 2348 949T          1
2470 199T 2348 949T          2
2470 199T 2348 949T          3
2470 199T 2348 949T          4
2470 199T 2348 949T          5
2470 199T 2349 699T          1
2470 199T 2349 699T          2
2470 199T 2349 699T          3
2470 199T 2349 699T          4
2470 199T 2356 649T          1
2470 199T 2356 649T          2
2470 199T 2356 649T          3
2470 199T 2379 399T          1
2470 199T 2379 399T          2
2470 199T 2383 299T          1
2470 199T 2470 199T          0