oracle with as 语法

一种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”便是在运行过程中构造的中间统计结果临时表。