oraclewithas语法.doc

oraclewithas语法
一种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_yWITH 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”关键字开头
可认为在真正进行查询之前预先构造了一个临时表secoole