WITH AS短语,也叫做子查询部分(subquery factoring),可以做很多事情,定义一个SQL片断,该SQL片断会被整个SQL语句所用到。有的时候,是为了让SQL语句的可读性更高些,也有可能是在UNION ALL的不同部分,作为提供数据的部分。
特别对于UNION ALL比较有用。因为UNION ALL的每个部分可能相同,但是如果每个部分都去执行一遍的话,则成本太高,所以可以使用WITH AS短语,则只要执行一遍即可。如果WITH AS短语所定义的表名被调用两次以上,则优化器会自动将WITH AS短语所获取的数据放入一个TEMP表里,如果只是被调用一次,则不会。而提示materialize则是强制将WITH AS短语里的数据放入一个全局临时表里。
一、with as 语法
单个语法:
with
tempName
as
(
select
....)
select
...
多个语法:
with
tempName1
as
(
select
....),
tempName2
as
(
select
....),
tempName3
as
(
select
....) ...
select
...
With查询语句不是以select开始的,而是以“WITH”关键字开头
可认为在真正进行查询之前预先构造了一个临时表TT,之后便可多次使用它做进一步的分析和处理
二、WITH AS实例
例:现在要从1-19中得到11-14。一般的sql如下:
select
*
from
(
--模拟生一个20行的数据
SELECT
LEVEL
AS
lv
FROM
DUAL
CONNECT
BY
LEVEL
<
20
) tt
WHERE
tt.lv
>
10
AND
tt.lv
<
15
使用With as 的SQL为:
with
TT
as
(
--模拟生一个20行的数据
SELECT
LEVEL
AS
lv
FROM
DUAL CONNECT
BY
LEVEL
<
20
)
select
lv
from
TT
WHERE
lv
>
10
AND
lv
<
15
多个临时表实例:
WITH
T3 AS
(
SELECT T1.ID, T1.CODE1, T2.DESCRIPTION
FROM TB_DATA T1, TB_CODE T2
WHERE T1.CODE1 = T2.CODE
),
T4 AS
(
SELECT T1.ID, T1.CODE2, T2.DESCRIPTION
FROM TB_DATA T1, TB_CODE T2
WHERE T1.CODE2 = T2.CODE
)
SELECT T3.ID, T3.DESCRIPTION, T4.DESCRIPTION
FROM T3, T4
WHERE T3.ID = T4.ID
ORDER BY ID;
三、WITH Clause方法的优点
增加了SQL的易读性,如果构造了多个子查询,结构会更清晰;更重要的是:“一次分析,多次使用”,这也是为什么会提供性能的地方,达到了“少读”的目标。
第一种使用子查询的方法表被扫描了两次,而使用WITH Clause方法,表仅被扫描一次。这样可以大大的提高数据分析和查询的效率。
另外,观察WITH Clause方法执行计划,其中“SYS_TEMP_XXXX”便是在运行过程中构造的中间统计结果临时表。
四、WITH AS 与增删改查结合用法
注意: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)