Oracle语法：with as关键字的使用

前言

在前几天排查问题的时候，发现有一条复杂的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只需要执行过一次就能在多个地方使用，也节省了数据库的资源。