oracle with..as用法

1、with的用法说明

      with语法中，with需要放在开头位置。其意义相当于在真正的查询语句之前，通过with构造一个临时表，也可构造出多个临时表。构造之后，其后面的查询语句可以将构造的临时表当成真正的表一样去查询和使用。显著的优点就是，“一次查询，多次使用”，从而起到了sql优化的作用。

2、with实战用法

（1）、构造单个临时表语法

with e as (select * from scott.emp e where e.empno=7499)
select * from e;

以上的用例，相当于先把scott库中的emp表的编码为7499的数据查询出来构造成e临时表，这里真实emp表查询*，所以临时表e的字段同真实表emp字段，临时表e的内容为真实表emp中编号为7499的内容。构造后，下面的查询语句为真实的查询语句，查询临时表e的全部数据！

（2）、构造多个临时表语法

with
     e as (select * from scott.emp),
     d as (select * from scott.dept)
select * from e, d where e.deptno = d.deptno;

以上相当于构造两张临时表e和d，真正查询出来的为临时表e和d中部门编码相同的数据

（3）、多次使用临时表

with
    sql1 as (select to_char(a) s_name from test_tempa),
    sql2 as (select to_char(b) s_name from test_tempb where not exists (select s_name from sql1 where rownum=1))
select * from sql1
union all
select * from sql2
union all
select 'no records' from test_aaa
       where not exists (select s_name from sql1 where rownum=1)
       and not exists (select s_name from sql2 where rownum=1);

以上的查询，可以看到构造的sql1和sql2多次被用到了，因为通过with的构造，所以查询的效率会比不构造要优化许多。通过unionall连接，需要保证以上三个真正的查询语句查询体的字段类型和别名一致。

（4）、与case  end连用

WITH uu_ids AS
 (SELECT DISTINCT (CASE
                    WHEN user_id IS NULL THEN
                     vid
                    ELSE
                     user_id
                  END) ids
    FROM t_record t
   WHERE 1 = 1
     AND QUESTION IS NOT NULL
     AND t.create_time >= to_date(?, 'yyyy-MM-dd HH24:Mi:SS')
     AND to_date(?, 'yyyy-MM-dd HH24:Mi:SS') >= t.create_time)
SELECT count(0)
  FROM (SELECT uu_ids.ids user_id, max(r.create_time) max_time
          FROM uu_ids, t_record r
         WHERE (r.user_id = uu_ids.ids OR r.vid = uu_ids.ids)
         GROUP BY uu_ids.ids) table_count

uu_ids为with构造的临时表。

在oracle中case..end中为一套逻辑的处理（包含when，then，else），本例为当user_id为空的时候查vid字段，不为空就是本身的user_id，付给别名ids。

（5）、with实现递归

with ret(id,name,parentId) as (
select id id,name name,parent_id parentId from sys_org where id = '1'
union all 
select c.id id,c.name name,c.parent_id parentId from sys_org c inner join ret p on p.id = c.parent_id
)
select id,name,parentId from ret ;

3、学海无涯苦作舟！见一个学一个吧！