SQL ServerSQL 中With as 的用法

转自:http://www.cnblogs.com/superyinhai/archive/2010/04/09/1708643.html

一.WITH AS的含义
WITHAS短语,也让做子查询有(subqueryfactoring),可以给你开多工作,定义一个
SQL片断,该SQL片断会被通SQL语句所用到。有的上,是为着给SQL语句的可读性更高些,也发出或是于UNION
ALL的差部分,作为提供数据的局部。
专程对UNION
ALL比较有因此。因为UNIONALL的每个有或者同,但是只要每个片都去实施同样全副的言辞,则成本不过强,所以可以采用WITH
AS短语,则使实施同样全即可。如果WITHAS短语所定义之表名被调用两蹩脚以上,则优化器会自动将WITHAS短语所抱的数量放入一个TEMP表里,
如果只是给调用一不成,则无见面。而唤醒materialize则是劫持将WITHAS短语里之数码放入一个大局临时表里。很多查询通过这种方法都得以增强速
度。
二.动方式
先行押下面一个嵌套的查询语句:

 

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

上面的查询语句以了一个子询问。虽然就漫漫SQL语句并无复杂,但要嵌套的层系过多,会要SQL语句很难以阅读与维护。因此,也可采取表变量的章程来解决这个题目,SQL语句如下:

declare @t table(CountryRegionCode nvarchar(3))
insert into @t(CountryRegionCode) (select CountryRegionCode from
person.CountryRegion where Name like ‘C%’)

select * from person.StateProvince where CountryRegionCode
in (select * from @t)

虽然上面的SQL语句要于第一栽方法重新复杂,但却将子查询在了表变量@t中,这样做用设SQL语句再度便于保障,但以见面带动其他一个题材,就
是性的损失。由于表变量实际上采取了临时表,从而增加了附加的I/O开销,因此,表变量的方并无顶适合数据量大且频繁查询的状态。为这个,在SQL
Server2005中提供了另外一种缓解方案,这就是是公用表表达式(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的数据表

  1. CTE 可以引用我,也得引用在相同 WITH 子句被先行定义之
    CTE。不容许前望引用。

  2. 不能在 CTE_query_definition 中利用以下子词:

(1)COMPUTE 或 COMPUTE BY

(2)ORDER BY(除非指定了 TOP 子句)

(3)INTO

(4)带有查询提示的 OPTION 子句

(5)FOR XML

(6)FOR BROWSE

  1. 要是将 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)

CTE除了可以简化嵌套SQL语句他,还可以拓展递归调用,

相关文章