sql with as 用法

系统管理员 2022-09-18 11:50 152阅读 0赞

sql with as 用法(适用sqlserver，好像oracle也适用)

Server 2005中提供了公用表表达式（CTE），使用CTE，可以使SQL语句的可维护性，同时，CTE要比表变量的效率高得多。

下面是CTE的语法：  
\[ WITH <common\_table\_expression> \[ ,n \] \]  
<common\_table\_expression>::=  
        expression\_name \[ ( column\_name \[ ,n \] ) \]  
    AS  
        ( CTE\_query\_definition )  
  
    现在使用CTE来解决上面的问题，SQL语句如下：  
  
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在处理公用表表达式的方式上有所不同。  
    在使用CTE时应注意如下几点：  
1. CTE后面必须直接跟使用CTE的SQL语句（如select、insert、update等），否则，CTE将失效。如下面的SQL语句将无法正常使用CTE：  
  
with  
cr as  
(  
    select CountryRegionCode from person.CountryRegion where Name like 'C%'  
)  
select \* from person.CountryRegion  -- 应将这条SQL语句去掉  
\-- 使用CTE的SQL语句应紧跟在相关的CTE后面 --  
select \* from person.StateProvince where CountryRegionCode in (select \* from cr)  
  
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  
  
3. 如果CTE的表达式名称与某个数据表或视图重名，则紧跟在该CTE后面的SQL语句使用的仍然是CTE，当然，后面的SQL语句使用的就是数据表或视图了，如下面的SQL语句所示：  
\--  table1是一个实际存在的表  
with  
table1 as  
(  
    select \* from persons where age < 30  
)  
select \* from table1  --  使用了名为table1的公共表表达式  
select \* from table1  --  使用了名为table1的数据表  
  
4. CTE 可以引用自身，也可以引用在同一 WITH 子句中预先定义的 CTE。不允许前向引用。  
  
5. 不能在 CTE\_query\_definition 中使用以下子句：  
（1）COMPUTE 或 COMPUTE BY  
（2）ORDER BY（除非指定了 TOP 子句）  
（3）INTO  
（4）带有查询提示的 OPTION 子句  
（5）FOR XML  
（6）FOR BROWSE  
  
6. 如果将 CTE 用在属于批处理的一部分的语句中，那么在它之前的语句必须以分号结尾，如下面的SQL所示：  
declare @s nvarchar(3)  
set @s = 'C%'  
;  -- 必须加分号  
with  
t\_tree as  
(  
    select CountryRegionCode from person.CountryRegion where Name like @s  
)  
select \* from person.StateProvince where CountryRegionCode in (select \* from t\_tree)  
  
7、CTE除了可以简化嵌套SQL语句外，还可以进行递归调用

参考：http://www.cnblogs.com/fightLonely/archive/2011/02/24/1963907.html