求一SQL語句，請高手幫忙BOM相關的查詢MTN(BOM表)
BOM_ID(成品), MATERIAL_ID(原料), STD_QTY(標準用量)
P1 M1 0.3
P1 M2 0.4
P1 M3 1
P1 M11 0.2
P1 M3111 0.7
M1 M11 0.5
M2 M21 0.5
M3 M31 0.8
M31 M311 0.6
M311 M3111 0.5希望得到
P1 M11 0.4 (0.3 + 0.2*0.5)
P1 M21 0.2 (0.4*0.5)
P1 M3111 0.94 (0.7 + 0.8 * 0.6 * 0.5)現實表中最多有5階, 每層原料數量在 80之內
請高手支招...
BOM_ID(成品), MATERIAL_ID(原料), STD_QTY(標準用量)
P1 M1 0.3
P1 M2 0.4
P1 M3 1
P1 M11 0.2
P1 M3111 0.7
M1 M11 0.5
M2 M21 0.5
M3 M31 0.8
M31 M311 0.6
M311 M3111 0.5希望得到
P1 M11 0.4 (0.3 + 0.2*0.5)
P1 M21 0.2 (0.4*0.5)
P1 M3111 0.94 (0.7 + 0.8 * 0.6 * 0.5)現實表中最多有5階, 每層原料數量在 80之內
請高手支招...
declare
qty 数据类型；
qty1 数据类型；
bom_id 数据类型；
i number;cursor c1 is
select bom_id,std_qty
from BOMcursor c2(param 数据类型) is
select material_id,std_qty
from BOM
WHERE bom_id=parambegin
open c1;
i:=1;
loop
fetch c1 into bom_id(i),qty(i);
qty2=qty(i);
i:=i+1;
open c2(qty(i));
fetch c2 into qty1(i);
qty=qty2+i*qty1(i);
if c2%NOTFOUND then
qty=0;
end if;
把结果插入到数据表
exit when c1%notfound
end loop;
close c1;
end ;
/
'P1' MID A
---- -------------------- ----------------
P1 M1 .3
P1 M11 .3*.5
P1 M11 .2
P1 M2 .4
P1 M21 .4*.5
P1 M3 1
P1 M31 1*.8
P1 M311 1*.8*.6
P1 M3111 1*.8*.6*.5
P1 M3111 .7如果能把列A解析出来在取sum就好了，呵呵
2. 因為BOM有多層， 用loop 能否判斷到第3，4，5階？
http://www.itpub.net/viewthread. ... p%3Bfilter%3Ddigest而且不知道支不支持小数点
--ps:你给出的m11的计算是错的,应该是0.2+03*0.5=0.35
实现方法:
CREATE OR REPLACE FUNCTION STR_TO_NUMBER(P_STR VARCHAR2) RETURN NUMBER AS
R NUMBER;
BEGIN
EXECUTE IMMEDIATE 'select ' || P_STR || ' from dual'
INTO R;
RETURN R;
END;
/
SELECT ROOT, MATERIAL_ID, SUM(Q)
FROM (SELECT O.*, STR_TO_NUMBER(O.N) Q
FROM (SELECT CONNECT_BY_ROOT BOM_ID ROOT,
MATERIAL_ID,
CONNECT_BY_ISLEAF LF,
LTRIM(SYS_CONNECT_BY_PATH(TO_CHAR(STD_QTY, '0.0'), '*'),
'*') N
FROM MTN S
CONNECT BY PRIOR MATERIAL_ID = BOM_ID
START WITH BOM_ID = 'P1') O
WHERE LF = 1)
GROUP BY ROOT, MATERIAL_ID
ORDER BY 1;
--输出
ROOT MATERIAL_ID SUM(Q)
---- ----------- -----
P1 M11 0.35
P1 M21 0.2
P1 M3111 0.94
(select 'P1',max(c) c,power(10,sum(log(10,d))) e from (SELECT id B,MID C,std D FROM MTN CONNECT BY PRIOR MID=ID START WITH ID='P1' ORDER BY b,c) t where t.b<>'P1' or t.c<>(select max(mid) from mtn where substr(mid,1,2)=substr(t.c,1,2)) group by substr(c,1,2)
union
select 'P1',c,d from (SELECT id B,MID C,std D FROM MTN CONNECT BY PRIOR MID=ID START WITH ID='P1' ORDER BY b,c) t where t.b='P1' and t.c=(select max(mid) from mtn where substr(mid,1,2)=substr(t.c,1,2)))
group by 'P1',c测试成功
---- -------------------- ---------
P1 M11 .35
P1 M21 .2
P1 M3111 .94
前面的略掉product_output data;cursor c1(dd varchar2,i number) is
sql语句(select count(partname) from ...)cursor c2(i number) is
sql语句 (select qty from ....)BEGIN
FOR i IN 1..60 LOOPopen c1('2007/12/31',i);
fetch c1 into product_count(i);open c2(i);
fetch c2 into product_output(i);
if c2%NOTFOUND then
product_output(i):=0;
end if;
fetch c2 into product_output(i);INSERT INTO fab1_output_age(repdate,age,output,count) VALUES (sysdate,i,product_output(i),product_count(i));COMMIT;
close c1;
close c2;END LOOP;END;
測試成功，太感謝了
不過TO_CHAR(STD_QTY, '0.0') 這個可以不用，可以自動轉換， 用了小位數多的會變0
(
BOM_ID VARCHAR2(50),
MATERIAL_ID VARCHAR2(50),
STD_QTY NUMBER,
BOMLEVEL NUMBER,
ISLEAF VARCHAR2(1)
)create or replace procedure
is
begin
delete from BOM_t ;
--初始化
insert into BOM_t
select t.*,1,0 from bom t where t.bom_id='P1'
;
loop --循环处理，直到没有需要处理的数据
insert into BOM_t
select t.bom_id,
nvl(t1.material_id,t.material_id),
sum(t.std_qty*nvl(t1.std_qty,1)),
t.BOMLEVEL+1,
case when t1.material_id is not null then 0 else 1 end
from bom_t t,
bom t1
where t1.bom_id(+)=t.material_id
and (t.BOMLEVEL,t.bom_id) in (select bomlevel,bom_id
from (
select tt.bomlevel,tt.bom_id,min(tt.isleaf) isleaf,max(tt.bomlevel) over(partition by null ) maxbomlevel
from bom_t tt
group by tt.bom_id,tt.bomlevel
)
where isleaf=0 and maxbomlevel=bomlevel
)
group by t.bom_id,nvl(t1.material_id,t.material_id),t.BOMLEVEL,case when t1.material_id is not null then 0 else 1 end
exit when sql%rowcount=0;
end loop;
--删除中间结果
delete from BOM_t t
where (t.BOMLEVEL,t.bom_id) not in( select max(tt.bomlevel),tt.bom_id
from bom_t tt
group by tt.bom_id)
commit;
end;
(
BOM_ID VARCHAR2(50),
MATERIAL_ID VARCHAR2(50),
STD_QTY NUMBER
)