BOM物料表从connect_by_isleaf说起

下面这样写是错的。写出这样的语句的实质就是自己误导自己。

with test_cc as (
  select 'A' root,'B' parent_root from dual union
  select 'B','C' from dual union
  select 'C','D' from dual union
  select 'C','E' from dual union
  select 'E','F' from dual
)
select level lv,
       connect_by_root(root) conn_root,
       connect_by_root(parent_root) conn_parent_root,
       --最后一个是A的才是leaf
       connect_by_isleaf,
       sys_connect_by_path(parent_root,'>')||'>'||root path
from test_cc
connect by prior root=parent_root;

[img]http://dl2.iteye.com/upload/attachment/0094/4949/ff1b15ae-b917-3693-be41-e6402891bb9b.jpg[/img]


下面这样写才是对的。

with test_cc as (
  select 'A' root,'B' parent_root from dual union
  select 'B','C' from dual union
  select 'C','D' from dual union
  select 'C','E' from dual union
  select 'E','F' from dual
)
select level lv,
       connect_by_isleaf,
       connect_by_root(root)||sys_connect_by_path(parent_root,'>') path
from test_cc
connect by prior parent_root=root;

[img]http://dl2.iteye.com/upload/attachment/0094/4947/d17f6881-d47a-3872-8055-8f30777338d9.jpg[/img]


这边有一个规则：

1.connect by prior 后面跟的是什么,sys_connect_by_path(的后面就要跟着同样的一个东西。否则路径会错。

2.并且不可以使用:root||sys_connect_by_path(parent_root,'>')来连。
必须用 connect_by_root(root)||sys_connect_by_path(parent_root,'>') 才能得到正确的结果。

3.这里还有个规则务必一定要记住：
root parent_root
B C
A B
B D
如果我们想 A,B 去跟上一行的 B,C 或下一行的 B,D 连接
正确的写法是：connect by prior parent_root= root;【1】
而不是写成： connect by parent_root= prior root;【2】
虽然他们表达的意思是一样的。但是对connect_by_isleaf有着相当大的影响。因为你【2】的写法实际上是你把整棵树倒过来了。

A.如果我们写成 connect by parent_root= prior root;会发现sys_connect_by_path的路径是对的。但是connect_by_isleafe的内容就是错的。它把整棵树倒过来了。根节点变成了叶子节点。【站在自己那里连对方】

B.如果我们写成 connect by prior parent_root = root;会发现sys_connect_by_path 的路径是错的而且还少了很多分支。但是connect_by_isleafe的内容是对的。【站在对方那里连自己】


有一个BOM结构表,如下:


[img]http://dl2.iteye.com/upload/attachment/0094/4953/e03eb124-3437-3e04-89b6-efedb061e444.jpg[/img]


首先我要得到顶级零件到最下级零件的全路径：

select level lv,
       connect_by_isleaf lf, 
       part_no,component_part,per_assembly,
       connect_by_root(part_no) rt_part_no,
       connect_by_root(component_part) rt_component_part,
       sys_connect_by_path(per_assembly,'>') path_value,
       connect_by_root(part_no)||sys_connect_by_path(component_part,'>') path_correct,
       substr(sys_connect_by_path(per_assembly,'*'),2,100) path_exp_value
from CY_BOM_COMPONENT --(select * from CY_BOM_COMPONENT where part_no='ABDEF')
connect by nocycle prior component_part = part_no 
order by path_correct;

[img]http://dl2.iteye.com/upload/attachment/0094/4955/eac41d7c-c14d-3428-beb0-9858c17488f7.jpg[/img]

[img]http://dl2.iteye.com/upload/attachment/0094/4957/60ef7da5-5aa7-31a7-9c72-63480b094d8c.jpg[/img]


然后我在这些数据的基础上group by.我只要那些叶子节点的内容求和即可得到单个最下级零件和所有零件的总数，因为我把每个叶子节点的地方放置了一个从根到叶子路径的所有值。

select * from(
    select level lv,
           connect_by_isleaf lf, 
           part_no,component_part,per_assembly,
           connect_by_root(part_no) rt_part_no,
           connect_by_root(component_part) rt_component_part,
           sys_connect_by_path(per_assembly,'>') path_value,
           connect_by_root(part_no)||sys_connect_by_path(component_part,'>') path_correct,
           substr(sys_connect_by_path(per_assembly,'*'),2,100) path_exp_value
    from CY_BOM_COMPONENT --(select * from CY_BOM_COMPONENT where part_no='ABDEF')
    connect by nocycle prior component_part = part_no 
)where lf=1
order by path_correct;

最后算总的结果是：

create or replace
function f_calcExpValue(expr varchar2) return number is r number;
begin
	execute immediate 'select '||expr||' from dual' into r;
	return r;
end f_calcExpValue;

select --wm_concat(lv) lv,
       --max(lf) lf,
       rt_part_no,
       --wm_concat(path_correct) path_correct_group,
       --wm_concat(path_exp_value) path_exp_group,
       --replace(wm_concat(path_exp_value),',','+') path_exp_value
       f_calcExpValue(replace(wm_concat(path_exp_value),',','+')) path_exp_value
--select *
from(
    select level lv,
           connect_by_isleaf lf, 
           part_no,component_part,per_assembly,
           connect_by_root(part_no) rt_part_no,
           connect_by_root(component_part) rt_component_part,
           sys_connect_by_path(per_assembly,'>') path_value,
           connect_by_root(part_no)||sys_connect_by_path(component_part,'>') path_correct,
           substr(sys_connect_by_path(per_assembly,'*'),2,100) path_exp_value
    from CY_BOM_COMPONENT --(select * from CY_BOM_COMPONENT where part_no='ABDEF')
    connect by nocycle prior component_part = part_no 
)where lf=1
group by rt_part_no
order by rt_part_no;

[img]http://dl2.iteye.com/upload/attachment/0094/4959/10e16b1c-f823-31ff-8488-710fae2e340b.jpg[/img]

create or replace
function f_calcExpValue(expr varchar2) return number is r number;
begin
	execute immediate 'select '||expr||' from dual' into r;
	return r;
end f_calcExpValue;


select wm_concat(lv) lv,
       max(lf) lf,
       rt_part_no,
       wm_concat(path_correct) path_correct_group,
       --wm_concat(path_exp_value) path_exp_group,
       --replace(wm_concat(path_exp_value),',','+') path_exp_value
       f_calcExpValue(replace(wm_concat(path_exp_value),',','+')) path_exp_value,
       wm_concat(path_exp_value_single) path_exp_value_single_group
--select *
from(
    select lv,lf,rt_part_no,path_correct,path_exp_value,
           component_part||':'||f_calcExpValue(path_exp_value) path_exp_value_single
    from(
        select level lv,
               connect_by_isleaf lf, 
               part_no,component_part,per_assembly,
               connect_by_root(part_no) rt_part_no,
               connect_by_root(component_part) rt_component_part,
               sys_connect_by_path(per_assembly,'>') path_value,
               connect_by_root(part_no)||sys_connect_by_path(component_part,'>') path_correct,
               substr(sys_connect_by_path(per_assembly,'*'),2,100) path_exp_value
        from CY_BOM_COMPONENT --(select * from CY_BOM_COMPONENT where part_no='ABDEF')
        connect by nocycle prior component_part = part_no 
    )where lf=1
)
group by rt_part_no
order by rt_part_no;

[img]http://dl2.iteye.com/upload/attachment/0094/4961/3fe04ad1-c062-3b7d-84d3-f1072bb52c9f.jpg[/img]

select rt_part_no as 顶级零件,
       wm_concat(path_exp_value_single) 需各最下级零件的个数,
       f_calcExpValue(replace(wm_concat(path_exp_value),',','+')) as 需最下级零件总个数
from(
    select lv,lf,rt_part_no,path_correct,path_exp_value,
           component_part||':'||f_calcExpValue(path_exp_value) path_exp_value_single
    from(
        select level lv,
               connect_by_isleaf lf, 
               part_no,component_part,per_assembly,
               connect_by_root(part_no) rt_part_no,
               connect_by_root(component_part) rt_component_part,
               sys_connect_by_path(per_assembly,'>') path_value,
               connect_by_root(part_no)||sys_connect_by_path(component_part,'>') path_correct,
               substr(sys_connect_by_path(per_assembly,'*'),2,100) path_exp_value
        from CY_BOM_COMPONENT --(select * from CY_BOM_COMPONENT where part_no='ABDEF')
        connect by nocycle prior component_part = part_no 
    )where lf=1
)group by rt_part_no
order by rt_part_no;

[img]http://dl2.iteye.com/upload/attachment/0094/4967/6aeeb939-e96a-3f69-879a-43a0725fccf5.jpg[/img]