WITH AS 的含义
WITH AS 短语,也叫做子查询部分(subquery factoring),可以定义一个sql片断,该片断会被整个sql语句所用到,特别对于UNION ALL比较有用。因为UNION ALL 的每个部分可能相同,但是如果每个部分都去执行一遍的话,成本太高,所以可以使用WITH AS语句,只需要执行一遍即可。
WITH AS 的优点
1.增加了sql的易读性,如果构造了多个子查询,结构会更清晰
2.提高效率,一次构建,多次使用。如果with as 短语定义的表名被调用两次以上,则优化器会自动把with as 短语所获取的数据放入临时表中,如果只是被调用一次,则不会。提示materialize则是强制将with as短语中的数据放到一个全局临时表中。在执行计划中 SYS_TEMP_XXXXX就是临时表+
WITH AS 的语法
-- 单个表
with tmpTableName as (select * from tb_name)
select * from tmpTableName
-- 加materialize
with tmpTableName as
(select /*+ materialize */ t.* from tb_name t)
select * from tmpTableName
-- 多个表
with tmp1 as (select * from tb_1),
tmp2 as (select * from tb_2)
select * from tmp1,tmp2 where tmp1.id = tmp2.id
WITH AS 与增删改查结合
学生表:
成绩表:
- select查询
with t1 as
(select * from STUDENT),
t2 as
(select * from score)
select * from t1, t2 where t1.stuid = t2.stuid
2. insert插入
插入语句中 with as 要放在insert的后面
insert into STUDENT
with t1 as
(select * from STUDENT t)
select t1.stuid + 10 as stuid,
t1.stuname || '1' as stuname,
t1.stusex,
t1.stuclass
from t1
where t1.stuid = '5'
3. delete删除
同上,删除的时候也不能把with as 放在前面
delete from STUDENT t
where t.stuid in (with t1 as (select * from STUDENT t)
select t1.stuid from t1 where t1.stuid = '15'
)
- update更新
update STUDENT t
set t.stuname =
(with t1 as (select * from STUDENT t)
select t1.stuname || '55555555'
from t1
where t1.stuid = '5')
where t.stuid = '5'
总结
基本上,我使用with as用得最多的地方是在select查询上,其他地方看你的情况了,例子比较简单,理解就好。