注意:1. with必须紧跟引用的select语句
2.with创建的临时表必须被引用,否则报错
4.1与select查询语句结合使用
查询同一个单据编号对应的借款单和核销单中,借款金额不相等的单据
with verificationInfo as (select ment.fnumber, sum(t.famount) vLoanSum, ment.fnumber "单据编号", sum(t.famount) "核销单中借款总额" from shenzhenjm.t_finance_expenseremburseitem t left join shenzhenjm.t_finance_expenserembursement ment on ment.fid = t.fkrembursementid where 1 = 1 group by ment.fnumber), loanInfo as (select ment.fnumber, sum(t.famount) loanSum, ment.fnumber "单据编号", sum(t.famount) "借款单中借款总额" from shenzhenjm.t_finance_expenseremburseitem2 t left join shenzhenjm.t_finance_expenserembursement ment on ment.fid = t.fkrembursementid where 1 = 1 group by ment.fnumber) select * from verificationInfo v, loanInfo l where l.fnumber = v.fnumber and l.loanSum != v.vLoanSum;
4.2与insert结合使用
如下的with as语句,不能放在insert前,而是放在紧接着要调用的地方前
要求将同一个单据编号对应的借款单和核销单中,借款金额不相等的单据,对应的借款单删除,并将对应的核销单插入到借款单表中 (借款单和核销单表结构完全一样)
insert into T_finance_ExpenseRemburseItem2
(FID,
FKREMBURSEMENTID,
FAMOUNT,
FKCREATEBYID,
FCREATETIME,
FKCUID,
FKCOSTTYPEID,
FCOSTTYPENAME)
with verificationInfo as
(select ment.fnumber,
sum(t.famount) vLoanSum,
ment.fnumber "单据编号",
sum(t.famount) "核销单中借款总额"
from shenzhenjm.t_finance_expenseremburseitem t
left join shenzhenjm.t_finance_expenserembursement ment
on ment.fid = t.fkrembursementid
where 1 = 1
group by ment.fnumber),
loanInfo as
(select ment.fnumber,
sum(t.famount) loanSum,
ment.fnumber "单据编号",
sum(t.famount) "借款单中借款总额"
from shenzhenjm.t_finance_expenseremburseitem2 t
left join shenzhenjm.t_finance_expenserembursement ment
on ment.fid = t.fkrembursementid
where 1 = 1
group by ment.fnumber)
select sys_guid(),
ment.fid,
t.famount,
ment.fkcreatebyid,
ment.fcreatetime,
ment.fkcuid,
t.fkcosttypeid,
t.fcosttypename
from T_finance_ExpenseRemburseItem t
left join t_finance_expenserembursement ment
on ment.fid = t.fkrembursementid
where 1 = 1
and exists (select *
from verificationInfo v, loanInfo l
where l.fnumber = v.fnumber
and l.loanSum != v.vLoanSum
and v.fnumber = ment.fnumber);
4.3 与delete删除结合使用
delete from t_finance_expenseremburseitem2 item2 where exists(with temp as (select t.fnumber, sum(item1.famount) vloanSum, sum(item1.frealityamount) vSum, sum(item2.famount) loanSum from t_finance_expenserembursement t left join t_finance_expenseremburseitem item1 on item1.fkrembursementid = t.fid left join t_finance_expenseremburseitem2 item2 on item2.fkrembursementid = t.fid where 1 = 1 and t.frembursementtype = 'LOAN_REPORT' and to_char(t.fcreatetime, 'yyyy') > '2017' group by t.fnumber order by t.fnumber asc) select 1 from temp t left join t_finance_expenserembursement ment on t.fnumber = ment.fnumber left join t_finance_expenseremburseitem2 item on item.fkrembursementid = ment.fid where t.vloanSum != t.loanSum and item.fid = item2.fid);
4.4与update结合使用
update dest b set b.NAME = (with t as (select * from temp) select a.NAME from temp a where a.ID = b.ID)