ORACLE WITH AS 用法

记得以前在论坛里看到inthirties用到过WITH AS这个字眼，当时没特别在意。今天在一个帖子里又看到有人用这个，所以就去网上搜了搜相关内容，自己小试了一把，写下来，方便以后忘了的话学习。

 

===================================================================================

 

语法：

with tempName as (select ....)
select ...

 

 

例：现在要从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行的数据
	SELECTLEVELAS lv  FROM DUAL  CONNECT  BYLEVEL<<>20<<> ) 
 select lv from TT WHERE lv >10AND lv <<>15<<><<><<><<>

 

一种SQL查询方法，颠覆日常以select开始的SQL查询写法

create table t (x number(10), y number(10));

insert into t values (1,110);

insert into t values (2,120);

insert into t values (2,80);

insert into t values (3,150);

insert into t values (3,30);

insert into t values (3,60);

commit;

 

select * from t;

 

 

需求描述

按照x列分组后统计y列的总值，最终目标是选出比y列总值的三分之一大的那些分组统计信息

 

使用子查询方式实现

最容易想到的方法

SELECT x, SUM (y) AS total_y

    FROM t

  GROUP BY x

  HAVING SUM (y) > (SELECT SUM (y) / 3 FROM t)

  ORDER BY total_y

 

 

 

WITH Clause方法闪亮登场

WITH secooler_sum AS (SELECT x, SUM (y) total_y

                         FROM t

                       GROUP BY x)

 SELECT x, total_y

   FROM secooler_sum

  WHERE total_y > (SELECT SUM (total_y) / 3 FROM secooler_sum)

 ORDER BY total_y

 

查询语句不是以select开始的，而是以“WITH”关键字开头

可认为在真正进行查询之前预先构造了一个临时表secooler_sum，之后便可多次使用它做进一步的分析和处理

 

WITH Clause方法的优点

增加了SQL的易读性，如果构造了多个子查询，结构会更清晰；

更重要的是：“一次分析，多次使用”，这也是为什么会提供性能的地方，达到了“少读”的目标

 

知其所以然

为什么WITH Clause方法会提高效率？通过查看上面两种方法的执行计划便可略知一二

1）使用子查询的执行计划

set autot trace exp

 

第一种使用子查询的方法T表被扫描了两次，而使用WITH Clause方法，T表仅被扫描一次

这也是为什么在大型数据仓库系统中推荐使用WITH Clause方法进行查询统计的原因，这样可以大大的提高数据分析和查询的效率

 

另外，观察WITH Clause方法执行计划，其中“SYS_TEMP_XXXX”便是在运行过程中构造的中间统计结果临时表。

 

 

先举个例子吧：

有两张表，分别为A、B，求得一个字段的值先在表A中寻找，如果A表中存在数据，则输出A表的值；如果A表中不存在，则在B表中寻找，若B表中有相应记录，则输出B表的值；如果B表中也不存在，则输出"no records”字符串。

 

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 dual
      where not exists (select s_name from sql1 where rownum=1)
      and not exists (select s_name from sql2 where rownum=1);