数据库：SQLServer中with as 用法笔记

一、with as 概念介绍

with as 也叫做子查询部分（subquery factoring），可以定义一个SQL段落，该SQL段落可以被整个SQL语句所用到类似于临时表的作用。with as 可以提高你的SQL语句的可读性，也有可以用在在UNION ALL的不同部分，作为提供临时数据的部分。

二、with as 用途

with as对于UNION ALL查询语句非常有用。因为UNION ALL的每个查询部分可能相同，但是如果每个部分都去执行一遍的话，则查询成本很高，如果使用WITH AS段落查询，则只要执行一遍即可。如果with as段落所定义的表名被调用两次以上，则SQLServer会自动将with asS段落所获取的数据放入一个临时表里，如果只是被调用一次，则不会放到临时表里面。而提示materialize则是强制将WITH AS短语里的数据放入一个全局临时表里。很多查询通过这种方法都可以提高速度。

三、什么是CTE

CTE完整的描述是公用表表达式（Common Table Expression)是SQL SERVER 2005版本之后引入的一个新特性。CTE可以看作是一个临时的查询结果集，可以在接下来的一个SELECT,INSERT,UPDATE,DELETE,MERGESQL语句中被多次引用。使用公用表达式可以让SQL语句更加可读。、

四、CTE有下面四个优点：

1、可以定义递归公用表表达式(CTE)

2、当需要将查询结果集作为视图被多个地方引用时，CTE可以使SQL更加简洁

3、GROUP BY语句可以直接作用于子查询所得的标量列

4、可以在一个语句中多次引用公用表表达式(CTE)

五、CTE语法格式

with table1as(查询语句)select  * from table1示例：withtb1 as(    select name.age,address from person.T_user where name like 'a%')select * from tb1

六、CTE 注意事项

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

withtb1 as(    select name.age,address from person.T_user where name like 'a%')select * from T_userselect * from tb1  -- 查询将会失效

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

with table1as(CTE_query_definition) , --用逗号分割table2  as(查询语句)...select  * from table1 ,table2

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

withT_user as(    select * from T_user where name='小王')select * from T_user  -- 查询结果为T_user的公共表表达式select * from T_user  --  查询结果为T_user的数据表

1. CTE 可以引用自身，也可以引用在同一 WITH 子句中预先定义的 CTE。不允许前向引用。

with tb as(select * from T_user where id=2),tb2 as(select * from tb where id=2)select * from tb -- tb2 可以调用tb，不可以反向调用

1. 不能在 CTE\_query\_definition 中使用以下子句：

（1）COMPUTE 或 COMPUTE BY

（2）ORDER BY（除非指定了 TOP 子句）

（3）INTO

（4）带有查询提示的 OPTION 子句

（5）FOR XML

（6）FOR BROWSE

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

declare @s nvarchar(3)
set @1 = 100;  -- 必须加分号withtb1 as(    select name.age,address from person.T_user where name like 'a%')select * from tb1

以上是SQLServer中with as 用法介绍，希望能对大家有所 帮助，感兴趣的欢迎交流！

IT技术分享社区

个人博客网站：https://programmerblog.xyz

文章推荐程序员效率：画流程图常用的工具程序员效率：整理常用的在线笔记软件远程办公：常用的远程协助软件，你都知道吗？51单片机程序下载、ISP及串口基础知识硬件：断路器、接触器、继电器基础知识