Oracle数据库SQL复杂查询、数据处理（一、使用WITH AS提高性能简化嵌套SQL）

• 原博文链接 http://www.cnblogs.com/fygh/archive/2011/08/31/2160266.html

一、WITH AS的含义

WITH AS短语，也叫做子查询部分（subquery factoring），可以让你做很多事情，定义一个SQL片断，该SQL片断会被整个SQL语句所用到。有的时候，是为了让SQL语句的可读性更高些，也有可能是在UNION ALL的不同部分，作为提供数据的部分。
特别对于UNION ALL比较有用。因为UNION ALL的每个部分可能相同，但是如果每个部分都去执行一遍的话，则成本太高，所以可以使用WITH AS短语，则只要执行一遍即可。如果WITH AS短语所定义的表名被调用两次以上，则优化器会自动将WITH AS短语所获取的数据放入一个TEMP表里，如果只是被调用一次，则不会。 而提示materialize则是强制将WITH AS短语里的数据放入一个全局临时表里。很多查询通过这种方法都可以提高速度。

二、实际用法

SQL Server 2005中提供了另外一种解决方案，这就是公用表表达式（CTE），使用CTE，可以增加SQL语句的可维护性，同时，CTE要比表变量的效率高得多。

用法：

  [ WITH <common_table_expression> [ ,n ] ] 
        <common_table_expression>::= 
                expression_name [ ( column_name [ ,n ] ) ] 
            AS 
                 ( CTE_query_definition )

例子：
嵌套SQL语句：

 select * from person.StateProvince where CountryRegionCode in 
                 (select CountryRegionCode from person.CountryRegion where Name like 'C%') 

用CTE解决：

with 
cr as 
( 
select CountryRegionCode from person.CountryRegion where Name like 'C%' 
) 
select * from person.StateProvince where CountryRegionCode in (select * from cr)

其中cr是一个公用表表达式，该表达式在使用上与表变量类似，只是SQL Server 2005在处理公用表表达式的方式上有所不同。

三、详解：

1. CTE后面必须直接跟使用CTE的SQL语句（如select、insert、update等），否则，CTE将失效。

2. CTE后面也可以跟其他的CTE，但只能使用一个with，多个CTE中间用逗号（,）分隔，如下面的SQL语句所示：

with 
cte1 as 
( 
    select * from table1 where name like 'abc%' 
), 
cte2 as 
( 
    select * from table2 where id > 20 
), 
cte3 as 
( 
    select * from table3 where price < 100 
) 
select a.* from cte1 a, cte2 b, cte3 c where a.id = b.id and a.id = c.id