sql里面 with...as 是什么意思啊？如何使用

with...as是用来定义sql代码片段的语句，一般是在sql嵌套查询比较多的时候应用，可以增加sql语句的可读性。下面我以sql server来具体演示一下with...as怎么用：

1、准备要 *** 作的数据，假设要查询下面年龄大于40岁的数据，如下图所示

2、用with...as将查询结果定义成一个表cr，cr不是硬性要求，你可以自己定义成喜欢的名字，如下图所示

3、然后就可以直接从cr表中查询数据，如下图所示

4，最后我们可以看到需要的数据从cr中被查询出来了，如下图所示

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短语里的数据放入一个全局临时表里。很多查询通过这种方法都可以提高速度。

二．使用方法

先看下面一个嵌套的查询语句：

复制代码

代码如下:

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

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在处理公用表表达式的方式上有所不同。