时间戳

首页 > 解决方案 > Oracle/SQL：深度，层次关系

sql - Oracle/SQL：深度，层次关系

问题描述

在这件事上我需要你的帮助；

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查询中实现一个代码来添加到这个查询的输出中，一个新的字段Depth，其原理如下：

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)，对于其他记录，该记录必须具有 Depth = 0；Fiedl2(949T) 的第一个值有 5 个深度，(699T) 有 4 个深度，依此类推。

标签： sqloraclehierarchy

解决方案

这为给定输入提供了所需的输出：

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

推荐阅读