BOM算法

Question

请教各位有没有效率比较好的算法;
我有一张数据表的结构是:
字段A 字段B  字段C
表里面的记录有:
A B NULL
A C NULL
E F NULL
B E NULL
B F NULL
H I NULL
H J NULL
J K NULL
K P NULL
F U NULL
F B NULL
C A NULL我想通过SQL算法,生成一张新的表, 表的结果是
A 001
B 001
C 001
E 001
F 001
U 001
H 002
I 002
J 002
K 002
P 002也就是说 我想通过递归的关系, 只要是有直接关系或者有间接关系的归类成一组.
不知道有什么比较好的效率的算法,请教各位了!

Answer 1

设原表为TA，目标表为TB[code=SQL]
建立临时表TMP_TAB
CREATE GLOBAL TEMPORARY TABLE TMP_TAB
(
    ID                             VARCHAR2(10),
    VAL                            VARCHAR2(500)
)
CREATE OR REPLACE PROCEDURE BOM
IS
CURSOR CUR IS SELECT * FROM TA;
CUR_RD  TA%ROWTYPE;
TYPE T_SK IS TABLE OF VARCHAR2(500) INDEX BY BINARY_INTEGER;
SK T_SK;
I NUMBER;   -- FOR LOOP
IDX NUMBER; -- INDEX OF LOOP
PT1 NUMBER; -- POINTER 1
PT2 NUMBER; -- POINTER 2
BEGIN
  I := 0;
  FOR CUR_RD IN CUR LOOP
    IF I = 0 THEN 
      I := I + 1;
      SK(I) := CUR_RD.A || CUR_RD.B;
    ELSE
      PT1 := 0;
      PT2 := 0;
      FOR IDX IN 1..I LOOP
        IF INSTR(SK(IDX),CUR_RD.A) > 0 THEN
          PT1 := IDX;
        END IF;
        IF INSTR(SK(IDX),CUR_RD.B) > 0 THEN
          PT2 := IDX;
        END IF;
      END LOOP;
      IF PT1 > 0 AND PT2 > 0 THEN
        IF PT1 <> PT2 THEN
          SK(PT1) := SK(PT1) || SK(PT2);
          SK(PT2) := '';
        END IF;
        NULL;
      ELSE
        IF PT1 > 0 THEN
          SK(PT1) := SK(PT1) || CUR_RD.B;
        END IF;
        IF PT2 > 0 THEN
          SK(PT2) := SK(PT2) || CUR_RD.A;
        END IF;
        IF PT1 = 0 AND PT2 = 0 THEN
          I := I + 1;
          SK(I) := CUR_RD.A || CUR_RD.B;
        END IF;
      END IF;
    END IF;
  END LOOP;
  I := 1;
  FOR IDX IN SK.FIRST..SK.LAST LOOP
    IF NVL(LENGTH(SK(IDX)),0) > 0 THEN
      INSERT INTO TMP_TAB(ID,VAL) VALUES(LPAD(I,3,'0'),SK(IDX));
      I := I + 1;
    END IF;
  END LOOP;
  INSERT INTO TB (SELECT T1.A,T2.ID FROM TA T1,TMP_TAB T2 WHERE INSTR(T2.VAL,T1.A) > 0
                 UNION
                 SELECT T1.B,T2.ID FROM TA T1,TMP_TAB T2 WHERE INSTR(T2.VAL,T1.B) > 0) ;
  COMMIT;
END;code]

Answer 2

又见循环~
呵呵,貌似BOM算法还真的没有什么好的算法.

Answer 3

http://bbs.erp100.com/archiver/tid-7191.html
来自 corcass 的意见：人家告诉我最好用标准exploder_userexit..速度较快..不会有考虑不周全的地方..使用方法如下(转)
declare
l_grp_id NUMBER ; 
l_session_id NUMBER ; 
l_org_id NUMBER ; 
l_levels_to_explode NUMBER := 10 ; 
l_module NUMBER DEFAULT 2 ; -- BOM = 2 
l_cst_type_id NUMBER DEFAULT -1 ; -- All cost = -1 else 0 
l_item_id NUMBER ; 
l_bom_or_eng NUMBER ; 
l_using_req_quantity NUMBER ; 
l_using_ass_dem_date DATE ; 
l_rev_date VARCHAR2(20):= to_char(sysdate,'DD-MON-YY HH24:MI'); 
l_err_msg VARCHAR2(250) ; 
l_error_code NUMBER ; BEGIN 
-- 首先要有一个Group id 
SELECT bom_explosion_temp_s.NEXTVAL 
INTO l_grp_id 
FROM dual ; -- 其次要得到一个Session id 
SELECT bom_explosion_temp_session_s.NEXTVAL 
INTO l_session_id 
FROM dual ; bompexpl.exploder_userexit( 
verify_flag =>0, 
org_id =>4, 
order_by =>1, 
grp_id =>l_grp_id, 
session_id =>l_session_id, 
levels_to_explode =>10,--l_levels_to_explode, 
bom_or_eng =>1,--l_bom_or_eng, 
impl_flag =>1, 
plan_factor_flag =>2, 
explode_option =>2, -- 2, 
module =>l_module, 
cst_type_id =>l_cst_type_id, 
std_comp_flag =>2, 
expl_qty =>1, 
item_id =>114910,--l_item_id, 
alt_desg =>'', 
comp_code =>'', 
rev_date =>l_rev_date, 
err_msg =>l_err_msg, 
error_code =>l_error_code 
); END ; /*运行结束后用以下语句得到结果. 
SELECT bom_explosion_temp_s.currval 
FROM dual 
SELECT * FROM bom_explosion_temp bet 
WHERE bet.group_id = 199388 */某个ITEM 的 BOM /ROUTING 全有了select DISTINCT 
msi1.segment1 P_item,
to_char(b.lvl) LV,
c.Department_Code,
nvl(bom.alternate_bom_designator,'P') Alternate_bom_designator,
msi2.segment1 C_item,
c.Usage,
b.component_quantity Component_Qty
from inv.mtl_system_items_b msi1,
inv.mtl_system_items_b msi2,
bom.bom_bill_of_materials bom,
(select level lvl,
bic.bill_sequence_id,
bic.component_item_id,
bic.component_quantity,
bic.COMPONENT_YIELD_FACTOR,
bic.item_num,
bic.wip_supply_type,
bic.supply_subinventory,
bic.effectivity_date
FROM bom.bom_inventory_components bic
where disable_date IS NULL
start with bic.bill_sequence_id in 
( select bill_sequence_id 
from bom.bom_bill_of_materials bom2,
inv.mtl_system_items_b msi
where bom2.assembly_item_id = msi.inventory_item_id
and bom2.organization_id = msi.organization_id
and msi.segment1 = 'FSC13AA4BMNCBB'
and msi.organization_id = 83
and bom2.alternate_bom_designator is null
)
CONNECT BY bic.bill_sequence_id = 
prior (SELECT distinct bill_sequence_id
FROM bom.bom_bill_of_materials BO,
inv.mtl_system_items_b msi
WHERE BO.assembly_item_id = bic.component_item_id
AND BO.organization_id = 83
and bo.ORGANIZATION_ID = msi.ORGANIZATION_ID
and bo.ASSEMBLY_ITEM_ID = msi.INVENTORY_ITEM_ID
and bo.alternate_bom_designator is null
and disable_date IS NULL
)
) b,
(select msib.inventory_item_id inventory_item_id,
nvl(bor.alternate_routing_designator,'P') Alternate, 
bd.department_code Department_Code,
sum(bore.usage_rate_or_amount) Usage
from inv.mtl_system_items_b msib,
bom.bom_operational_routings bor,
bom.bom_operation_sequences bos,
bom.bom_operation_resources bore,
bom.bom_departments bd,
bom. bom_resources br
where msib.inventory_item_id = bor.assembly_item_id
and msib.organization_id = bor.organization_id
and msib.organization_id = 83
and bor.routing_sequence_id = bos.routing_sequence_id
and bos.operation_sequence_id = bore.operation_sequence_id
and bos.department_id = bd.department_id
and bore.resource_id = br.resource_id 
and bos.DISABLE_DATE is null 
and bor.alternate_routing_designator is null
and bore.usage_rate_or_amount != 0
group by msib.inventory_item_id, bor.alternate_routing_designator, bd.department_code 
) c 
where b.bill_sequence_id = bom.bill_sequence_id
and bom.ORGANIZATION_ID = 83
and bom.ORGANIZATION_ID = msi1.ORGANIZATION_ID
and bom.ASSEMBLY_ITEM_ID = msi1.INVENTORY_ITEM_ID
and bom.ORGANIZATION_ID = MSI2.ORGANIZATION_ID
AND b.component_item_id = MSI2.INVENTORY_ITEM_ID
AND MSI2.SEGMENT1 not lIKE 'F9%'
AND MSI2.SEGMENT1 not like 'R__B%'
and msi2.segment1 not like 'F8%'
and msi2.segment1 not like 'R9%'
and msi2.segment1 not like 'Z%'
and bom.ASSEMBLY_ITEM_ID = c.inventory_item_id 
order by 2

Answer 4

新的一论记录, 只要能满足以下结果, 就OK了. 好象是地图, 只要能互相通的城市(直接关系或者间接关系),那就分为一个区域. 完全搭不上边的 那就再开一个区域, 依次类推...
请教各位有没有效率比较好的算法;
我有一张数据表的结构是:
字段A 字段B  字段C
表里面的记录有:
A        B        NULL
A        C        NULL
B        A        NULL
H        I        NULL
H        J        NULL
J        K        NULL
K        P        NULL
F        U        NULL
F        B        NULL
E        F        NULL
C        A        NULL
B        E        NULL
B        F        NULL我想通过SQL算法,生成一张新的表, 表的结果是
A        001
B        001
C        001
E        001
F        001
U        001
H        002
I        002
J        002
K        002
P        002我再次增加一条记录：
p        U        NULL那出现的结果应该为：
A        001
B        001
C        001
E        001
F        001
U        001
H        001
I        001
J        001
K        001
P        001从上面的结果可以看出P和U是直接关系， 这样导致了原来的A--U的那组间接的和H--P的那组有关系了，所以最后合并成一组了。当然其中还可以同时存在P  U，U   P这样的记录。
也就是说 我想通过递归的关系, 只要是有直接关系或者间接关系的归类成一组.

Answer 5

exploder_userexit 用的表怎么都是dual, 具体怎么用呢?

Answer 6

随便写了一个，效率可能不怎样：
select regexp_substr(str,'[^0-9]+') type,regexp_substr(str,'[0-9]+') id from(
select regexp_substr(get_bom,'[^,]+',1,rownum) str from dual
connect by rownum<length(regexp_replace(get_bom,'[^,]',''))+1)
order by 2,1
/TYPE  ID
----- -----
a     001
b     001
c     001
e     001
f     001
u     001
h     002
i     002
j     002
k     002
p     002已选择11行。get_bom是我写的一个函数：
create or replace function get_bom return varchar2 as
type arraytype is table of varchar2(100);
v_list arraytype;
cnt number;
str varchar2(100):='';
flag number:=0;
begin
v_list:=arraytype();
for i in (select a,b from t2) loop
if v_list.count=0 then
select WMSYS.WM_CONCAT(a)||','||WMSYS.WM_CONCAT(b) into str
from(
select a,b from t2 start with a=i.a connect by nocycle prior b=a);
v_list.extend(1);
v_list(1):=str;
end if;
flag:=0;
for j in 1..v_list.count loop
select count(*) into cnt from dual where v_list(j) like '%'||i.a||'%';
if cnt>0 then
flag:=1;
exit;
else
select WMSYS.WM_CONCAT(a)||','||WMSYS.WM_CONCAT(b) into str
from(
select a,b from t2 start with a=i.a connect by nocycle prior b=a);
select count(*) into cnt from dual where str like '%'||v_list(j)||'%';
if cnt>0 then
v_list(j):=str;
flag:=1;
exit;
else
null;
end if;
end if;
end loop;
if flag=0 then
v_list.extend(1);
v_list(v_list.count):=str;
end if;
end loop;
str:='';
for k in 1..v_list.count loop
select WMSYS.WM_CONCAT(ch) into v_list(k) from(
select distinct regexp_substr(v_list(k),'[^,]+',1,rownum) ch
from dual
connect by
rownum<length(regexp_replace(v_list(k),'[^,]',''))+2);
str:=str||','||regexp_replace(v_list(k),'([^,]+)','00'||k||'\1');
end loop;
return str;
end;
/

Answer 7

呵呵，我觉得效率的话不会太慢，因为至少没有标规模的嵌套。
不过，也不敢说效率就高。愿意和大家交流。
下面说说思路：
是这样，建立基于表T1的游标循环，
把第一条记录的A和B都放到一个集合（后面我用“分组数组”这个词汇代表集合）中作为一个元素，
其后的每条记录，分4种情况讨论。
1、当前记录的A、B分别出现于“分组数组”的两个不同元素中，则合并这两个“分组数组”的元素。
2、当前记录的A、B分别出现于“分组数组”的同一元素中，则什么也不做遍历下一条。
3、当前记录的A、B有一个出现在“分组数组”的某个元素中，则若A出现把B加入这个元素，若B出现把A加入这个元素。
   即是这个元素包含A,B
4、当前记录的A,B在“分组数组”的每个元素中都未出现，则新建一个数组元素。元素的值：A || B
遍历了T1全表后得到一个包含路径关系的数组
类似：
ary(1) -> 'ABCEFU'
ary(2) -> 'HIJKP'
建立临时表TMP_TAB
插入数据
id   val
001  'ABCEFU'
002  'HIJKP'
查询t1和tmp_tab形成一个试图，关联条件是字段A被包含于val
union
查询t1和tmp_tab形成一个试图，关联条件是字段B被包含于val
这么做的目的是把A、B满足条件的列都合并到一起，并且过滤重复的记录。
把这个试图插入到目标表。