table如下,org_item表示“原物料”,new_item表示“新物料”。
即原物料可以用新物料替代,同样,新物料也可以替代原物料
create table t(
org_item varchar(10),
new_item varchar(10)
);
insert into t values('a','d');
insert into t values('b','c');
insert into t values('b','d');
insert into t values('x','y');希望得到如下结果:
料号 群组号
a 1
b 1
c 1
d 1
x 2
y 2即:a、b、c、d是一组料号,它们相互可以替代;x、y是一组料号请教各位童鞋,该如何写SQL,procedure也可谢谢!
即原物料可以用新物料替代,同样,新物料也可以替代原物料
create table t(
org_item varchar(10),
new_item varchar(10)
);
insert into t values('a','d');
insert into t values('b','c');
insert into t values('b','d');
insert into t values('x','y');希望得到如下结果:
料号 群组号
a 1
b 1
c 1
d 1
x 2
y 2即:a、b、c、d是一组料号,它们相互可以替代;x、y是一组料号请教各位童鞋,该如何写SQL,procedure也可谢谢!
declare
v_group_id number;
v_org_item number;
v_new_item number;
v_insert_group_id number;
v_insert_item_id number;
begin
delete t2;
delete t_result;
insert into t2
select org_item, new_item, rownum from t;
loop
if v_group_id is not null then
select max(group_id), max(new_item)
into v_insert_group_id, v_insert_item_id
from (select a.group_id, new_item
from t2 a, t_result b, t_result c
where a.org_item = b.item
and a.new_item = c.item(+)
and b.group_id = v_group_id
and c.item is null
union
select a.group_id, org_item
from t2 a, t_result b, t_result c
where a.new_item = b.item
and a.org_item = c.item(+)
and b.group_id = v_group_id
and c.item is null) a
where rownum = 1;
insert into t_result
select v_insert_item_id, v_group_id
from dual
where v_insert_item_id is not null;
delete t2 where group_id = v_insert_group_id;
end if;
if v_group_id is null or sql%rowcount < 1 then
select max(group_id), max(org_item), max(new_item)
into v_group_id, v_org_item, v_new_item
from t2
where rownum = 1;
if v_group_id is not null then
delete t2 where group_id = v_group_id;
insert into t_result values (v_org_item, v_group_id);
insert into t_result values (v_new_item, v_group_id);
end if;
end if;
exit when v_group_id is null;
end loop;
end;