select s.BUS_CODE,s.PERIOD,s.MOP_ORDER,s.MOP_OPR_CODE,s.MOP_ITM_CODE,s.MOP_MOR_NO,
s.MOP_LOTNUM,s.MOP_CURRENTQTY,
s.MONEY,s.PERMONEY,t.ALLMONEY
from
(select * from wipcost a
where not exists(select 1 from wipcost where mop_lotnum=a.mop_lotnum and period=a.period and mop_order>a.MOP_ORDER)
and a.PERIOD='200901') t,
(select * from wipcost a
where a.mop_order>1 and
not exists(select 1 from wipcost where mop_lotnum=a.mop_lotnum and period=a.period and mop_order<a.MOP_ORDER)
and a.PERIOD='200902' )s
where t.mop_lotnum(+)=s.mop_lotnum
上面這段代碼其實就是對 wipcost 這一個表進行查詢.
(select * from wipcost a
where not exists(select 1 from wipcost where mop_lotnum=a.mop_lotnum and period=a.period and mop_order>a.MOP_ORDER)
and a.PERIOD='200901') t 這個是200901期的資料,而且是相同mop_lotnum 取最大mop_order的記錄,
(select * from wipcost a
where a.mop_order>1 and
not exists(select 1 from wipcost where mop_lotnum=a.mop_lotnum and period=a.period and mop_order<a.MOP_ORDER)
and a.PERIOD='200902' )s 這個是抓200902期的資料,而且是相同mop_lotnum 取最小mop_order的記錄但除了mop_order=1我現在就是想 更新 200902期且相同mop_lotnum 取最小mop_order的記錄除了mop_order=1 的記錄中 PERMONEY值.
用200901期 而且是相同mop_lotnum 取最大mop_order的記錄中的 ALLMONEY來更新.
不知道如何寫這個update語句.這個是表結構.
CREATE TABLE WIPCOST
(
BUS_CODE CHAR(5),
PERIOD VARCHAR2(6),
MOP_ORDER NUMBER(9),
MOP_OPR_CODE VARCHAR2(20),
MOP_ITM_CODE VARCHAR2(20),
MOP_MOR_NO VARCHAR2(20),
MOP_LOTNUM VARCHAR2(20),
MOP_CURRENTQTY NUMBER,
MONEY NUMBER,
PERMONEY NUMBER,
ALLMONEY NUMBER
)
--------------------------------------------------
這是表中的一些數據.
21100 200901 1 物料裁剪 CGD-D113A-D2-1 AMO081216009 AMO0812160090001.00 29848 406.51008 0 406.51008
21100 200901 2 電腦鑽孔 CGD-D113A-D2-1 AMO081216009 AMO0812160090001.00 29848 0 406.51008 406.51008
21100 200901 3 刀模成型 CGD-D113A-D2-1 AMO081216009 AMO0812160090001.00 29848 0 406.51008 406.51008
21100 200901 4 轉換站 CGD-D113A-D2-1 AMO081216009 AMO0812160090001.00 29848 0 406.51008 406.51008
update WIPCOST s set PERMONEY=(select ALLMONEY from (select * from wipcost a
where not exists(select 1 from wipcost where mop_lotnum=a.mop_lotnum and period=a.period and mop_order>a.MOP_ORDER)
and a.PERIOD='200901') t where t.mop_lotnum(+)=s.mop_lotnum)
where s.mop_order>1 and
not exists(select 1 from wipcost where mop_lotnum=s.mop_lotnum and period=s.period and mop_order<s.MOP_ORDER)
and a.PERIOD='200902';
UPDATE wipcost a
SET PERMONEY = (SELECT MAX(ALLMONEY) KEEP(dense_rank LAST ORDER BY mop_order)
FROM wipcost
WHERE PERIOD = '200901'
AND mop_lotnum = a.mop_lotnum)
WHERE a.mop_order > 1
AND NOT EXISTS (SELECT 1
FROM wipcost
WHERE mop_lotnum = a.mop_lotnum
AND period = a.period
AND mop_order < a.MOP_ORDER)
AND a.PERIOD = '200902'
where not exists(select 1 from wipcost where mop_lotnum=a.mop_lotnum and period=a.period and mop_order>a.MOP_ORDER)
and a.PERIOD='200901') t where t.mop_lotnum(+)=s.mop_lotnum)
where s.mop_order>1 and
not exists(select 1 from wipcost where mop_lotnum=s.mop_lotnum and period=s.period and mop_order<s.MOP_ORDER)
and s.PERIOD='200902';