前言
在前几天排查问题的时候,发现有一条复杂的sql里面使用到了with alias as 的sql语法。原先从来没有见过,查询后发现是oracle特有的写法,主要用于将可能多次使用、查询过程较为耗时的sql语句进行封装,从而实现执行一次,多次调用,节省数据库资源。
也可以这么理解,with..as相当于一张中间表,可以简单理解为sql片段(类似java复用代码)。
语法
--单个别名
with aliasName as (select ....)
select ...
-- 多个别名
with
tmp as (select * from tb_name),
tmp2 as (select * from tb_name2),
tmp3 as (select * from tb_name3),
…
举例:
with temp as
(select 'xiaoming' as student_name from dual)
select case
when (select * from temp) = 'xiaoming' then
'Equal'
when (select * from temp) = 'xiaoqi' then
'Not Equals'
else
'Unknown'
end is_equals
from dual;
在例子中,将select 'xiaoming' as student_name from dual抽取出来(类似于一张临时表),这样在接下来使用到表的时候就可以使用定义的别名来反复使用,无需多次执行查询,本质上是对一种优化sql性能的解决方式。
同时,with as 的这种语法适合和union all搭配使用,因为使用union all拼接出来的结果集可能包含有重复项,如果对某个sql语句有重复使用的话,提前针对该语句进行抽取,在数据量比较大的情况下可以有效的提高性能。
with temp1 as
(select 'female' sex, 'zhangsan' stu_name from dual),
temp2 as
(select 'male' sex, 'lisi' stu_name from dual),
temp3 as
(select 'female' sex, 'wangwu' stu_name from dual)
select *
from temp1
union all
select *
from temp2
union all
select * from temp3
总结
总结下来,使用with as关键字有两个优点,一个是抽取sql出来后可以让复杂的sql语句变得更加简洁,从性能方面考虑,抽取出来的sql只需要执行过一次就能在多个地方使用,也节省了数据库的资源。