SQL入門之10 MERGE

語法 + 一個例子

[@more@]

-- 語法
MERGE INTO table_name AS table_alias
USING (table|view|sub_query) AS alias
ON (join condition)
WHEN MATCHED THEN
UPDATE SET
col1 = col_val1,
col2 = col2_val
WHEN NOT MATCHED THEN
INSERT (column_list)
VALUES (column_values);

-- 一個例子
merge into tj_rb_wlzx using
(select
v_tjrq as dt,
v_webid as web_id ,
nvl(cc.flag_stat,0) service_type, --受理分類 service_type
nvl(cc.type_id,0) taxtype, --稅種 taxtype
cc.swjg swjg,
sum(nvl(aa.slsl,0)) slsl,
sum(nvl(bb.clsl,0)) clsl,
sum(nvl(cc.ljsl,0)) ljsl
from
(
select nvl(a.flag_stat,0) flag_stat,a.type_id,a.swjg,count(1) slsl
from rl_ct_doc_item a
where a.item_id= v_itemid
and a.create_time between v_tjrq and v_tjrq+1
group by a.flag_stat,a.type_id,a.swjg
) aa, --當天受理數
( select nvl(t3.flag_stat,0) flag_stat,t3.type_id,t3.swjg,count(1) clsl
from rl_ct_doc_item t3
where t3.item_id= v_itemid
and t3.create_time between v_tjrq
and v_tjrq+1
and get_if_end_mv(t3.doc_id,0)=1 --關聯finished=1
group by t3.flag_stat,t3.type_id,t3.swjg
) bb , --當天處理數
( select nvl(c.flag_stat,0) flag_stat,c.type_id,nvl(c.swjg,'14100000000') swjg,count(1) as ljsl
from rl_ct_doc_item c
where c.item_id= v_itemid
and c.create_time between trunc(v_tjrq,'year') -- 年初
and v_tjrq+1 -- 日終
group by nvl(c.flag_stat,0),c.type_id,c.swjg
) cc -- 年累計

where cc.flag_stat=aa.flag_stat(+) -- 受理分類
and cc.type_id=aa.type_id(+) -- 稅種
and cc.swjg=aa.swjg(+) -- 稅務機關
and cc.flag_stat=bb.flag_stat(+)
and cc.type_id=bb.type_id(+)
and cc.swjg=bb.swjg(+)
group by cc.flag_stat,cc.type_id,cc.swjg
) v_source
on (v_source.dt=tj_rb_wlzx.dt and
v_source.web_id=tj_rb_wlzx.web_id and
v_source.service_type=tj_rb_wlzx.service_type and
v_source.taxtype=tj_rb_wlzx.taxtype and
v_source.swjg=tj_rb_wlzx.swjg )
when matched then
update set tj_rb_wlzx.slsl=v_source.slsl,
tj_rb_wlzx.clsl=v_source.clsl,
tj_rb_wlzx.ljsl=v_source.ljsl
when not matched then
insert (dt,web_id,service_type,taxtype,swjg,slsl,clsl,ljsl)
values (v_source.dt,v_source.web_id,
v_source.service_type,v_source.taxtype,v_source.swjg,
v_source.slsl,v_source.clsl,v_source.ljsl)
;