表1
ND ZHDYDM DYDM
2006 b1 b9
2006 b1 b10
2006 b1 b5
2006 b1 b6
2006 b1 b11
2006 b1 b12
2006 b1 b8
2006 b2 b9
2006 b2 b10
2006 b2 b5
2006 b3 b6
2006 b3 b11
2006 b3 b12
2006 b3 b8
2006 b4 b9
2006 b4 b10
2006 b5 b5
2006 b6 b6
2006 b7 b11
2006 b7 b12
2006 b8 b8
2006 b9 b9
2006 b10 b10
2006 b11 b11
2006 b12 b12
要求通过上面的表实现下面的表
表2
ND ZHDYDM DYDM
2006 b1 b2
2006 b1 b3
2006 b2 b4
2006 b2 b5
2006 b3 b6
2006 b3 b7
2006 b3 b8
2006 b4 b9
2006 b4 b10
2006 b7 b11
2006 b7 b12即通过表1给定的叶结点关系,计算出表2的子结点关系,树结构如下
|--b9
|--b4|
| |--b10
|--b2|
| |
| |--b5
|
b1|
| |--b6
| |
| | |--b11
| |--b7|
|--b3| |--b12
|
|--b8
想了很长时间没有一个很好的思路,大家帮忙想想,多谢!!!!!
ND ZHDYDM DYDM
2006 b1 b9
2006 b1 b10
2006 b1 b5
2006 b1 b6
2006 b1 b11
2006 b1 b12
2006 b1 b8
2006 b2 b9
2006 b2 b10
2006 b2 b5
2006 b3 b6
2006 b3 b11
2006 b3 b12
2006 b3 b8
2006 b4 b9
2006 b4 b10
2006 b5 b5
2006 b6 b6
2006 b7 b11
2006 b7 b12
2006 b8 b8
2006 b9 b9
2006 b10 b10
2006 b11 b11
2006 b12 b12
要求通过上面的表实现下面的表
表2
ND ZHDYDM DYDM
2006 b1 b2
2006 b1 b3
2006 b2 b4
2006 b2 b5
2006 b3 b6
2006 b3 b7
2006 b3 b8
2006 b4 b9
2006 b4 b10
2006 b7 b11
2006 b7 b12即通过表1给定的叶结点关系,计算出表2的子结点关系,树结构如下
|--b9
|--b4|
| |--b10
|--b2|
| |
| |--b5
|
b1|
| |--b6
| |
| | |--b11
| |--b7|
|--b3| |--b12
|
|--b8
想了很长时间没有一个很好的思路,大家帮忙想想,多谢!!!!!
|--b9
|--b4|
| |--b10
|--b2|
| |
| |--b5
|
b1|
| |--b6
| |
| | |--b11
| |--b7|
|--b3| |--b12
|
|--b8
2 (select nd, zhdydm, dydm , lag(zhdydm,1) over(partition by nd, dydm order
by to_number(substr(zhdydm,2))) as pre_dydm,
3 lead(zhdydm,1) over(partition by nd, dydm order by to_number(subst
r(zhdydm,2))) as next_dydm
4 from testa
5 order by 2,5
6 )
7 where next_dydm is not null ;ND ZHDYDM NEXT_DYDM
---------- ---------- ----------
2006 b1 b2
2006 b1 b3
2006 b2 b4
2006 b2 b5
2006 b3 b6
2006 b3 b7
2006 b3 b8
2006 b4 b10
2006 b4 b9
2006 b7 b11
2006 b7 b12已选择11行。如果数据不是这么有规律的话,上面的语句就没有办法得到正确的顺序
cDydm VARCHAR2(10); CURSOR cur_dydm IS
SELECT zhdydm FROM (SELECT zhdydm, COUNT(dydm) i FROM test GROUP BY zhdydm) k WHERE k.i = 1;BEGIN OPEN cur_dydm;
LOOP
FETCH cur_dydm
INTO cdydm;
EXIT WHEN cur_dydm%NOTFOUND;
BEGIN
SELECT DISTINCT zhdydm
INTO cZhdydm
FROM test
WHERE zhdydm IN (SELECT zhdydm
FROM test
WHERE dydm = cDydm
AND zhdydm <> dydm)
GROUP BY zhdydm
HAVING COUNT(*) = 2;
INSERT INTO test_1 (nd, zhdydm, dydm) VALUES ('2006', cZhdydm, cDydm);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
CLOSE cur_dydm;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
CLOSE cur_dydm;
END gyf_test;说明:
1. 根据B*Tree的特点,可以用SELECT zhdydm FROM (SELECT zhdydm, COUNT(dydm) i FROM test GROUP BY zhdydm) k WHERE k.i = 1;找出所有的末节点.
2. 在B*Tree中,一个末节点可能有多重父节点(比如父节点上还有父节点),而每个父节点的成员(不一定是直接子成员,可能是子成员的子成员)可能不一样,但离该节点最近的父节点的子成员一定最少.SELECT DISTINCT zhdydm
INTO cZhdydm
FROM test
WHERE zhdydm IN (SELECT zhdydm
FROM test
WHERE dydm = cDydm
AND zhdydm <> dydm)
GROUP BY zhdydm
这里使用了having count(*) = 2的意思假设这个B*tree是一棵二叉树.根据上面两点就能找到末节点最近的父节点.同理,把找出来的父节点删除,重建这棵树就能找被删除节点的父节点.具体的楼住可以参考一下.上面的存储过程执行的结果为:
2006 b7 b12
2006 b4 b9
2006 b4 b10
2006 b7 b11
把表中的以上关系删除,可以找出下一级的节点关系.
to xiaoxiao1984(笨猫儿):zhdydm不是那么有规律的,呵呵!
to ColinGan(浪子) :having count(*) = 2 好像不行吧,树结构的节点不一定就是这么多,我只是为了测试,弄了这么十几个节点而已!
SELECT DISTINCT zhdydm,count(*)
INTO cZhdydm,iCount
FROM test
WHERE zhdydm IN (SELECT zhdydm
FROM test
WHERE dydm = cDydm
AND zhdydm <> dydm)
GROUP BY zhdydm
iCount最小的为最近的父节点.
---------- ---------- ----------
2006 b2 b5
2006 b3 b6
2006 b3 b8
2006 b4 b9
2006 b4 b10
2006 b7 b11
2006 b7 b12
2006 b1 b2
2006 b1 b3
2006 b2 b4
2006 b3 b7
2006 b1 b2
2006 b1 b3已选择13行。demo@XIAOXIAO>select *from testd;未选定行demo@XIAOXIAO>select *from testa;ND ZHDYDM DYDM PREDYDM
---------- ---------- ---------- ----------
2006 b1 b9 b9
2006 b1 b10 b10
2006 b1 b5 b5
2006 b1 b6 b6
2006 b1 b11 b11
2006 b1 b12 b12
2006 b1 b8 b8
2006 b2 b9 b9
2006 b2 b10 b10
2006 b2 b5 b5
2006 b3 b6 b6
2006 b3 b11 b11
2006 b3 b12 b12
2006 b3 b8 b8
2006 b4 b9 b9
2006 b4 b10 b10
2006 b5 b5 b5
2006 b6 b6 b6
2006 b7 b11 b11
2006 b7 b12 b12
2006 b8 b8 b8
2006 b9 b9 b9
2006 b10 b10 b10
2006 b11 b11 b11
2006 b12 b12 b12
2006 b1 b4 b4
2006 b1 b7 b7
2006 b1 b3 b3
2006 b1 b2 b2
2006 b2 b4 b4
2006 b3 b7 b7已选择31行。demo@XIAOXIAO>exec p_gyf_testa;PL/SQL 过程已成功完成。demo@XIAOXIAO>select *from testd;ND ZHDYDM DYDM
---------- ---------- ----------
2006 b4 b10
2006 b7 b11
2006 b7 b12
2006 b2 b5
2006 b3 b6
2006 b3 b8
2006 b4 b9
2006 b1 b2
2006 b1 b3
2006 b2 b4
2006 b3 b7已选择11行。
l_lnt VARCHAR2(10) default 'N';cursor l_cursor is select distinct zhdydm from testa a
where a.zhdydm = a.predydm
and not exists
(select 1 from testd b where a.predydm = b.dydm );BEGINfor y in l_cursor loop --------had to changed
l_lnt := 'N';
begin
begin
select zhdydm into l_zhdydm from
(select zhdydm, row_number()over(order by l_childcnt) as rn from
(select zhdydm, count(1)as l_childcnt from testa a
where exists
(select zhdydm from testa b
where a.zhdydm = b.zhdydm
and predydm = y.zhdydm
and predydm <> zhdydm
)
group by zhdydm
)
)
where rn = 1;
exception when no_data_found then
dbms_output.put_line('==========循环到头节点啦===============');
l_lnt := 'Y' ;
end;
if l_lnt = 'N' then
execute immediate 'insert into testd values (''2006'',:1,:2)'
using l_zhdydm, y.zhdydm;
execute immediate 'update testa set predydm = :1 where zhdydm <> predydm and predydm = :2'
using l_zhdydm, y.zhdydm;
end if;
exception when others then
dbms_output.put_line('=========插入节点数据出错拉===========');
end ;
END LOOP;
COMMIT;
END gyf_testa;create or replace procedure p_gyf_testa is
l_count1 number default 0 ;
l_count2 number default 0 ;begin
loop
select count(distinct zhdydm) into l_count1 from testa a
where a.zhdydm <> a.predydm
and not exists
(select 1 from testd b
where b.dydm = a.predydm and b.zhdydm <> a.zhdydm
) ;
exit when l_count1 = 1 ;
gyf_testa;
end loop;
end p_gyf_testa;testa 表中补充了一部分数据,同时为了显示的更清楚,增加了一列predydm,楼主看看是这个意思么?
testb 表中是最后生成的数据