我正在尝试通过依赖于前一行计算结果的 CASE 语句进行计算。我正在使用的数据是分层数据。我的最终目标是构建结果数据以符合 Modified Preorder Tree Traversal algorithm .
这是我的原始数据:
+-------+--------+
| id | parent |
+-------+--------+
| 1 | (null) |
+-------+--------+
| 600 | 1 |
+-------+--------+
| 690 | 600 |
+-------+--------+
| 6990 | 690 |
+-------+--------+
| 6900 | 690 |
+-------+--------+
| 69300 | 6900 |
+-------+--------+
| 69400 | 6900 |
+-------+--------+
这就是我想要的最终结果。我很乐意详细说明为什么这是我正在寻找的，与 MPTT 等相关。
+-------+-----------+-----+------+--+--+--+--+
| id | parent_id | lft | rght | | | | |
+-------+-----------+-----+------+--+--+--+--+
| 1 | | 1 | 14 | | | | |
+-------+-----------+-----+------+--+--+--+--+
| 600 | 1 | 2 | 13 | | | | |
+-------+-----------+-----+------+--+--+--+--+
| 690 | 600 | 3 | 12 | | | | |
+-------+-----------+-----+------+--+--+--+--+
| 6900 | 690 | 4 | 9 | | | | |
+-------+-----------+-----+------+--+--+--+--+
| 6990 | 690 | 10 | 11 | | | | |
+-------+-----------+-----+------+--+--+--+--+
| 69300 | 6900 | 5 | 6 | | | | |
+-------+-----------+-----+------+--+--+--+--+
| 69400 | 6900 | 7 | 8 | | | | |
+-------+-----------+-----+------+--+--+--+--+
到目前为止，这是我的 SQL 代码的样子。它计算了我认为我在下面描述的算法需要的许多字段。这是企业设置中的“组织”数据，这就是 orgn 缩写在我的代码中很常见的原因。
这是我认为可以成功将其转换为 MPTT 格式的算法:
-If level is root (lvl=1), lft = 1, rght = subnodes*2 + 2
-If level is the next level down (lvl = prev_lvl+1), and prev_parent != parent (meaning this is the first sibling)
-lft = parent_lft+1
-If lvl = prev_lvl, so we are on the same level (don’t know if this is a true sibling of the same parent yet)
-if parent = prev_parent, lft=prev_rght+1 (true sibling, just use previous sibling’s right + 1)
-if parent != prev_parent, lft=parent_lft+1 (same level, not true sibling, so use parent’s left + 1)
-rght=(subnodes*2) + lft + 1
我目前的 SQL 代码:
WITH tab1 (
id,
parent_id
) AS (
SELECT
1,
NULL
FROM
dual
UNION ALL
SELECT
600,
1
FROM
dual
UNION ALL
SELECT
690,
600
FROM
dual
UNION ALL
SELECT
6990,
690
FROM
dual
UNION ALL
SELECT
6900,
690
FROM
dual
UNION ALL
SELECT
69300,
6900
FROM
dual
UNION ALL
SELECT
69400,
6900
FROM
dual
),t1 (
id,
parent_id,
lvl
) AS (
SELECT
id,
parent_id,
1 AS lvl
FROM
tab1
WHERE
parent_id IS NULL
UNION ALL
SELECT
t2.id,
t2.parent_id,
lvl + 1
FROM
tab1 t2,
t1
WHERE
t2.parent_id = t1.id
)
SEARCH BREADTH FIRST BY id SET order1,orgn_subnodes AS (
SELECT
id AS id,
COUNT(*) - 1 AS subnodes
FROM
(
SELECT
CONNECT_BY_ROOT ( t1.id ) AS id
FROM
t1
CONNECT BY
PRIOR t1.id = t1.parent_id
)
GROUP BY
id
),orgn_partial_data AS (
SELECT
orgn_subnodes.id AS id,
orgn_subnodes.subnodes,
parent_id,
lvl,
LAG(lvl,1) OVER(
ORDER BY
order1
) AS prev_lvl,
LAG(parent_id,1) OVER(
ORDER BY
order1
) AS prev_parent,
CASE
WHEN parent_id IS NULL THEN 1
END
lft,
CASE
WHEN parent_id IS NULL THEN ( subnodes * 2 ) + 2
END
rght,
order1
FROM
orgn_subnodes
JOIN t1 ON orgn_subnodes.id = t1.id
) SELECT
*
FROM
orgn_partial_data;
结果是:
+-------+----------+-----------+-----+----------+-------------+-----+------+--------+
| id | subnodes | parent_id | lvl | prev_lvl | prev_parent | lft | rght | order1 |
+-------+----------+-----------+-----+----------+-------------+-----+------+--------+
| 1 | 6 | | 1 | | | 1 | 14 | 1 |
+-------+----------+-----------+-----+----------+-------------+-----+------+--------+
| 600 | 5 | 1 | 2 | 1 | | | | 2 |
+-------+----------+-----------+-----+----------+-------------+-----+------+--------+
| 690 | 4 | 600 | 3 | 2 | 1 | | | 3 |
+-------+----------+-----------+-----+----------+-------------+-----+------+--------+
| 6900 | 2 | 690 | 4 | 3 | 600 | | | 4 |
+-------+----------+-----------+-----+----------+-------------+-----+------+--------+
| 6990 | 0 | 690 | 4 | 4 | 690 | | | 5 |
+-------+----------+-----------+-----+----------+-------------+-----+------+--------+
| 69300 | 0 | 6900 | 5 | 4 | 690 | | | 6 |
+-------+----------+-----------+-----+----------+-------------+-----+------+--------+
| 69400 | 0 | 6900 | 5 | 5 | 6900 | | | 7 |
+-------+----------+-----------+-----+----------+-------------+-----+------+--------+
我不关心树中“兄弟节点”的顺序。此外，如果您发现我开始使用的 SQL 没有用，您可以发布一个不使用任何 SQL 的答案。我发帖只是为了展示我认为我需要执行算法步骤的信息。
我将接受任何 Oracle 代码(数据库过程、SELECT 语句等)作为答案。
如果需要，请询问更多详细信息!
请您参考如下方法:
我认为开始的帖子有错字，对于 69400 应该是 (7, 8) 而不是 (4, 8)。
获得结果的规范方法是使用递归过程/函数。 下面的方法使用过程和临时表，但您可以通过函数返回集合来实现相同的目的。
临时表
create global temporary table tmp$ (id int, l int, r int) on commit delete rows;
包
create or replace package pkg as
procedure p(p_id in int);
end pkg;
/
sho err
包体
create or replace package body pkg as
seq int;
procedure p_(p_id in int) as
begin
seq := seq + 1;
insert into tmp$(id, l, r) values (p_id, seq, null);
for i in (select id from tab1 where parent_id = p_id order by id) loop
p_(i.id);
end loop;
seq := seq + 1;
update tmp$ set r = seq where id = p_id;
end;
procedure p(p_id in int) as
begin
seq := 0;
p_(p_id);
end;
end pkg;
/
sho err
在 SQL*PLus 中测试
SQL> exec pkg.p(1);
PL/SQL procedure successfully completed.
SQL> select * from tmp$;
ID L R
---------- ---------- ----------
1 1 14
600 2 13
690 3 12
6900 4 9
69300 5 6
69400 7 8
6990 10 11
7 rows selected.
更新
没有全局变量的独立过程
create or replace procedure p(p_id in int, seq in out int) as
begin
seq := seq + 1;
insert into tmp$(id, l, r) values (p_id, seq, null);
for i in (select id from tab1 where parent_id = p_id order by id) loop
p(i.id, seq);
end loop;
seq := seq + 1;
update tmp$ set r = seq where id = p_id;
end;
/
在 SQL*PLus 中测试
SQL> var n number
SQL> exec :n := 0;
PL/SQL procedure successfully completed.
SQL> exec p(1, :n);
PL/SQL procedure successfully completed.
SQL> select * from tmp$;
ID L R
---------- ---------- ----------
1 1 14
600 2 13
690 3 12
6900 4 9
69300 5 6
69400 7 8
6990 10 11
7 rows selected.
