-、update
UPDATE ADB_SJ_YWCLZQXX A1 SET A1.BJE = A1.ZQME*NVL((
SELECT DECODE(A2.ZQXZDM, '10',
(SELECT B1.BQBJZ FROM ADB_DJ_ZQJXQCXX B1 WHERE B1.ZQDM = A1.ZQDM AND B1.BQQXR<=Q_DATE AND B1.BQJXR>Q_DATE),
(SELECT B2.FXJG FROM ADB_DJ_ZQFXZJZC B2 WHERE B2.ZQDM = A1.ZQDM AND B2.ZJZCCS = 0)
)
FROM ADB_DJ_ZQ A2
WHERE A2.ZQDM = A1.ZQDM
), 0)/100
WHERE A1.HTBH in (SELECT B1.HTBH FROM ADB_SJ_YQYWCL B1 WHERE B1.CJRQ = Q_DATE AND B1.YWLBDM = '343') ;
二、 union all
UPDATE ADB_SJ_ZMQYWZQXX A1 SET A1.BJE = A1.ZQME*NVL((
SELECT DECODE(A2.ZQXZDM, '36',
(SELECT B1.BQBJZ FROM ADB_DJ_ZQJXQCXX B1 WHERE B1.ZQDM = A1.ZQDM AND B1.BQQXR<=V_DATE AND B1.BQJXR>V_DATE),
(SELECT B2.FXJG FROM ADB_DJ_ZQFXZJZC B2 WHERE B2.ZQDM = A1.ZQDM AND B2.ZJZCCS = 0)
)
FROM ADB_DJ_ZQ A2
WHERE A2.ZQDM = A1.ZQDM
), 0)/100
WHERE A1.HTBH in (
SELECT B1.HTBH FROM ADB_SJ_ZMQXQYWCL B1 WHERE B1.JGRQ = V_DATE AND B1.YWLBDM IN ('318','326')
UNION ALL
SELECT B1.HTBH FROM ADB_SJ_ZMQHGYWCL B1 WHERE B1.SQJGRQ = V_DATE AND B1.YWLBDM IN ('731','732','741','742')
);
三、exists
delete from adb_xxfw_wbfjzqjxq a
where exists (select 0
from adb_tmp_sjhsz b
where a.zqdm = b.sjzj_1
and a.fxqc = TO_NUMBER(b.sjzj_2)
and a.jlztdm = b.sjzj_3
and a.sjqbbz = b.sjzj_4
and a.sjlybz = b.sjzj_5
and b.bm = 'ADB_XXFW_WBZQJXQCXX')
四、in
delete from adb_xxfw_wbfjzqjxq a
where a.zqdm in (SELECT ADB_XXFW_WBZQ.ZQDM
FROM ADB_XXFW_WBZQ
where TRUNC(sjgxsj, 'DD') >= v_run_dt
and zqxzdm = '10'
union all
SELECT ADB_XXFW_WBZQJXQCXX.ZQDM
FROM ADB_XXFW_WBZQJXQCXX
where TRUNC(sjgxsj, 'DD') >= v_run_dt)
五、months_between、floor、ceil
select a.zqdm,
a.fxqc qc,
b.fddqr,
a.maxjxr,
a.fxpl,
months_between(b.fddqr, a.maxjxr),
months_between(b.fddqr, a.maxjxr)/a.fxpl,
floor(months_between(b.fddqr, a.maxjxr)/a.fxpl) r1,
ceil(months_between(b.fddqr, a.maxjxr) / a.fxpl) rn --计算生成的期次数
from tmp_adb_xxfw_wbfjzqjxq_01 a, adb_xxfw_wbzq b
where a.zqdm = b.zqdm
and a.maxjxr < b.fddqr
六、sqrt(37) --37的平方根
select round(sqrt(37)), sqrt(37) from dual
七、
update t_bas_sales_department_626 s
set (s.code,s.leave,s.pickup_self,s.delivery) =(select ofs.code,ofs.leave,ofs.pickup_self,ofs.delivery from
(select distinct os.code,
fs.leave,
fs.pickup_self,
fs.delivery
from t_bas_sales_department_626 os
inner join
t_bas_sales_department@ows2foss2 fs
on os.code = fs.code
where os.active = 'Y'
and fs.active = 'Y'
AND (os.leave != fs.leave or
os.pickup_self != fs.pickup_self or
os.delivery != fs.delivery)) ofs
where s.code = ofs.code)
where exists (
select 1 from (select distinct os.code,
fs.leave,
fs.pickup_self,
fs.delivery
from t_bas_sales_department_626 os
inner join
t_bas_sales_department@ows2foss2 fs
on os.code = fs.code
where os.active = 'Y'
and fs.active = 'Y'
AND (os.leave != fs.leave or
os.pickup_self != fs.pickup_self or
os.delivery != fs.delivery)) ofs
where s.code = ofs.code
) and s.active='Y'