赞
踩
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 pathfrom test_ccconnect by prior root=parent_root;
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,'>') pathfrom test_ccconnect by prior parent_root=root;
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_valuefrom CY_BOM_COMPONENT --(select * from CY_BOM_COMPONENT where part_no='ABDEF')connect by nocycle prior component_part = part_no order by path_correct;
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=1order by path_correct;
create or replacefunction 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=1group by rt_part_noorder by rt_part_no;
create or replacefunction 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_noorder by rt_part_no;
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_noorder by rt_part_no;
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。