时间戳

首页 > 解决方案 > Oracle 层次总和（叶子到根的距离）

sql - Oracle 层次总和（叶子到根的距离）

问题描述

我想获得分层查询的帮助（Oracle 11gR2）。我很难处理这些问题......

事实上，这是一个 2 合 1 的问题（需要 2 种不同的方法）。

我正在寻找一种方法来获取从所有个人记录到根的距离（而不是相反）。我的数据在树状结构中：

CREATE TABLE MY_TREE
(ID_NAME VARCHAR2(1) PRIMARY KEY,
 PARENT_ID VARCHAR2(1),
 PARENT_DISTANCE NUMBER(2)
);

INSERT INTO MY_TREE (ID_NAME,PARENT_ID,PARENT_DISTANCE) VALUES('A',NULL,NULL);
INSERT INTO MY_TREE (ID_NAME,PARENT_ID,PARENT_DISTANCE) VALUES('B','A',1);
INSERT INTO MY_TREE (ID_NAME,PARENT_ID,PARENT_DISTANCE) VALUES('C','B',3);
INSERT INTO MY_TREE (ID_NAME,PARENT_ID,PARENT_DISTANCE) VALUES('D','B',5);
INSERT INTO MY_TREE (ID_NAME,PARENT_ID,PARENT_DISTANCE) VALUES('E','C',7);
INSERT INTO MY_TREE (ID_NAME,PARENT_ID,PARENT_DISTANCE) VALUES('F','D',11);
INSERT INTO MY_TREE (ID_NAME,PARENT_ID,PARENT_DISTANCE) VALUES('G','D',13);

从层次上看，我的数据看起来像这样（但我有多个独立的根和更多级别）：

在此处输入图像描述

在第一个方法中，我正在寻找一个可以给我这个结果的查询：

LEVEL ROOT NODE ID_NAME ROOT_DISTANCE
----- ---- ---- ------- -------------
1     A    null A       null
2     A    null B       1
3     A    B    C       4
4     A    B    E       11
3     A    B    D       6
4     A    D    F       17
4     A    D    G       19

在这个结果中，

在这个方法中，我将始终指定最多 2 个根。

第二种方法必须是一个 PL/SQL 脚本，它将执行相同的计算 (ROOT_DISTANCE)，但以迭代方式，并将结果写入新表中。我想运行这个脚本一次，所以所有的根（~1000）都会被处理。

这是我查看脚本的方式：

“性能观点”需要这个脚本，所以如果已经计算了一个元素（例如：由另一个叶子计算的拆分节点），我们需要停止计算并传递到下一个叶子，因为我们已经知道结果从那里到根。例如，如果系统计算 ECBA，然后计算 FDBA，则不应再次计算 BA 部分，因为它是在第一遍中完成的。

您可以对这两个问题中的一个或两个问题进行遮篷，但是对于这两个问题，我需要遮篷。

谢谢！

标签： sqloracleplsqltree

解决方案

试试这个：

WITH brumba(le_vel,root,node,id_name,root_distance) AS (
  SELECT 1 as le_vel, id_name as root, null as node, id_name, to_number(null) as root_distance  
  FROM MY_TREE WHERE parent_id IS NULL
  UNION ALL
  SELECT b.le_vel + 1, b.root, 
         CASE WHEN 1 < (
                SELECT count(*) FROM MY_TREE t1 WHERE t1.parent_id = t.parent_id
              )
              THEN t.parent_id ELSE b.node
         END,
         t.id_name, coalesce(b.root_distance,0)+t.parent_distance
  FROM MY_TREE t
  JOIN brumba b ON b.id_name = t.parent_id
)
SELECT * FROM brumba

演示：https ://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=d5c231055e989c3cbcd763f4b3d3033f

不需要使用 PL/SQL 的“第二个方法” - 上面的 SQL 将一次计算所有根节点（parent_id列中有 null）的结果。只需在上述查询中添加或
添加前缀即可。 上面的演示包含后一个选项的示例INSERT INTO tablename(col1,col2, ... colN) ...CREATE TABLE name AS ...
CREATE TABLE xxx AS query

推荐阅读