Oracle分层总和（从叶到根的距离）

Oracle分层总和（从叶到根的距离）

试试这个：

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

---

无需使用PL / SQL进行“第二种方法”-上面的SQL将立即计算所有根节点（

parent_id

列中为空）的结果。
只需添加一个前缀或者

INSERT INTO tablename(col1,col2, ... colN) ...

还是

CREATE TABLE nameAS ...

以上面的查询。
上面的演示包含后一个选项的示例

CREATE TABLE xxx AS query