SQL Server中With As的介绍与应用（一）–With As的介绍

前言

最早接触的SQL是从2000开始的，后来慢慢地都用了2008了，不过很多新的语法都没有用过，在这里要讲一下With As也是因为在项目中遇到了一个问题，后面在网上找了找发现的With As的用法可以实现，这一篇我就先简单介绍一下With As，下一篇中会列出来在项目中实现的应用。

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

WITH AS 语法

[ WITH <common_table_expression> [ ,n ] ]

<common_table_expression>::=

expression_name [ ( column_name [ ,n ] ) ]

AS

( CTE_query_definition )

With As使用方法

我们数据库中有两个表，一个商品信息表tbSpXinXi，一个库存表tbSpKc。先看一下两个表的结构：

tbSpXinXi

tbSpKc

我们要实现查商品名称最后一个字为”茶”的库存

最原始的写法，嵌套一个查询语句：

select * from tbSpKc where incode in 
(select incode from tbSpXinXi where fname like '%茶')

用变量表名的方法：

declare @tb table(incode varchar(20))
insert into @tb(incode)
select incode from tbSpXinXi where fname like '%茶'


select * from tbSpKc where incode in (select * from @tb)

接下来我们就直接换成With As的用法

with row as 
    (    
    select incode from tbSpXinXi where fname like '%茶'
    )
select * from tbSpKc where incode in (select * from row)

从上面来看，感觉用With As的语句比最原始的写法好像还麻烦了点，因为我这里举的例子只是为了让大家知道怎么用，使用这个可以组合很多类型。

With As使用时注意的问题

1.With As后面必须直接跟使用With As的SQL语句（如select、insert、update等），否则，With As将失效。如下面的SQL语句将无法正常使用With As。

with row as 
    (    
    select incode from tbSpXinXi where fname like '%茶'
    )
select * from tbGysXinXi  -- 加上这句下面的row就失效了 
--使用row必须跟在with row as的后面
select * from tbSpKc where incode in (select * from row)

2.With As后面也可以跟其他的As，但只能使用一个With，多个With As中间用逗号（,）分隔。

with xinxi as 
    (    
    select incode,fname from tbSpXinXi where fname like '%茶'
    ),
    kc as 
    (
    select * from tbSpKc where 1=1
    )
    
select * from xinxi a,kc b where a.incode=b.incode

3. 如果With As的表达式名称与某个数据表或视图重名，则紧跟在该With As后面的SQL语句使用的仍然是With As的名称，当然，后面的SQL语句使用的就是数据表或视图了

with tbSpKc as 
    (    
    select * from tbSpXinXi where incode='14004015'
    )
select * from tbSpKc  -- 使用了名为tbSpKc的公共表表达式 
select * from tbSpKc  -- 原来的tbSpKc表

4. With As可以引用自身，也可以引用在同一 WITH 子句中预先定义的公共表达式。但不允许前向引用。

5. 不能在 CTE_query_definition 中使用以下子句：

COMPUTE 或 COMPUTE BY
ORDER BY（除非指定了 TOP 子句）
INTO
带有查询提示的 OPTION 子句
FOR XML
FOR BROWSE

6. 如果将 With As用在属于批处理的一部分的语句中，那么在它之前的语句必须以分号结尾。

declare @fname varchar(20)
select @fname='%茶'
;with xinxi as --这里前面必须加分号
    (    
    select incode from tbSpXinXi where fname like @fname
    )
select * from tbSpKc where incode in (select * from xinxi)