公用表表达式（CTE） common table expression

WITH 命令用于为 SELECT 表达式返回的结果集定义别名。

-----公用表表达式CTE 语法WITH common_table_expression [, ...]common_table_expression 语法如下：view_identifier [ ( column_identifier [, ...] ) ] [ AS ]( query )column_identifier：如果指定了 column_identifier，则其数量必须与 query 返回的**列数**相匹配。 如果未指定任何名称，则列名称派生自 query。**query**：生成结果集的查询。------------------------------USE db_student;WITH LOVEAS(SELECT * FROM course WHERE score > 8)SELECT * FROMLOVEwith主要用法之一就是简化联合子查询的sql代码，先将要用的子查询定义成一个表，后面可以重复使用。在多级和较为复杂的查询中可以让SQL更清晰。WITH AAA AS (SELECT c.sRTC_ID,c.sRTR_ID,c.sRTC_ColumnName FROM System_ReportTool_Columns c),BBB AS (SELECT r.sRTR_ID,r.sRTR_ReportName FROM System_ReportTool_Reports r)SELECT * FROM AAA aLEFT JOIN BBB b ON a.sRTR_ID=b.sRTR_ID得到的结果是这样的(子查询中的全部列)WITH locs(id,name,parent)AS(SELECT * FROM tb_loc)SELECT * FROM locs

with的相关总结

1.使用with子句可以让子查询重用相同的with查询块,通过select调用（with子句只能被select查询块引用），一般在with查询用到多次情况下。在引用的select语句之前定义,同级只能定义with关键字只能使用一次,多个用逗号分割。

2.with子句的返回结果存到用户的临时表空间中，只做一次查询，反复使用,提高效率。

3.在同级select前有多个查询定义的时候，第1个用with，后面的不用with，并且用逗号隔开。

4.最后一个with 子句与下面的查询之间不能有逗号，只通过右括号分割,with 子句的查询必须用括号括起来

5.如果定义了with子句，而在查询中不使用，那么会报ora-32035 错误：未引用在with子句中定义的查询名。（至少一个with查询的name未被引用，解决方法是移除未被引用的with查询），注意：只要后面有引用的就可以，不一定非要在主查询中引用，比如后面的with查询也引用了，也是可以的。

WITH AS可以被紧跟着的一条SQL语句所使用多次，但不能被紧跟着的多条SQL语句使用。

WITH CTE_TestAS(SELECT * FROM Person_1)SELECT * FROM CTE_Test SELECT * FROM CTE_Test --这条语句会报错 **CTE后面必须紧跟使用CTE的SQL语句**

WITH CTE_Test1AS(SELECT * FROM Person_1),CTE_Test2AS(SELECT * FROM Person_2)------ union 上下 是一个SQL语句SELECT * FROM CTE_Test1UNIONSELECT * FROM CTE_Test2

6.前面的with子句定义的查询在后面的with子句中可以使用。但是一个with子句内部不能嵌套with子句，MSDN中说CTE可以嵌套，但sql不行，不知道是否跟版本有关系。

7.当一个查询块名字和一个表名或其他的对象相同时，解析器从内向外搜索，优先使用子查询块名字。

8.with查询的结果列有别名，引用的时候必须使用别名或*。

临时表

需要在临时数据库TempDB中通过I/O操作来创建表结构，一旦用户退出SQL Server环境则自动被删除。

优点：能够长久存储数据，可以建立索引，和普通的物理表一样，能存储大量数据

缺点：不方便使用，使用完之后要手动的drop，不然就会一直存在（此次连接关闭后就没了，就是退出当前会话）

#表示本地临时表，仅在当前会话中可见；

##表示全局临时表，在所有会话中都可见；

--方法一： select into from 表不需要存在SELECT into #filterList from dbo.table--方法二： insert into select表要先创建CREATE table #tegeb ( nm int , amount decimal, sta varchar ) insert into #tegeb SELECT nm, amount,staFROM dbo.order with(NOLOCK)-- 处理一个数据库死锁的异常时候，其中一个建议就是使用 NOLOCK 或者 READPAST -- with(NOLOCK) 可能把没有提交事务的数据也显示出来 -- with(READPAST) 会把被锁住的行不显示出来 SELECT * FROM #tegeb --临时表一直存在，直到链接关闭Truncate table #tegeb --清空数据DROP TABLE dbo.#tegeb --必须手动drop

表变量

在内存中以表结构的形式存在，其定义与变量一致，其使用与表类似，不需要产生I/O。

DECLARE @temp table ( nm int , amount decimal, sta varchar ) insert into @temp SELECT nm, amount,sta FROM dbo.orderSELECT * FROM @temp --单独运行最后一句，@temp已经不存在了

公用表表达式：Common Table Expression：

定义在内存中保存的临时存储结果集对象，不产生I/O，不需要按照表变量这样定义，使用方法和表类似。可以自己引用，也可以再查询中被多次引用。

WITH AS-做子查询部分(subquery factoring)。如果WITH AS所以定的表名被调用两次以上，则优化器会自动将WITH AS所获取的数据放入**临时表**里，如果只是被调用一次{还在内存中}，则不会。WITH AS可以被紧跟着的一条SQL语句所使用多次，但不能被紧跟着的多条SQL语句使用。如果将 CTE 用在属于批处理的一部分的语句中，那么在它之前的语句必须以分号结尾

CTE 分 递归CTE 和 非递归CTE

非递归公用表表达式(CTE)：

仅仅一次性返回一个结果集用于外部查询调用，并不在**其定义的语句中{就是CTE中的Query部分}**调用其自身的CTE

递归公用表表达式CTE：

对于递归公用表达式来说，只需要在语句中定义两部分：

基本语句（定位点成员）

递归语句（递归成员）

递归 CTE 定义至少必须包含两个 CTE 查询定义，一个定位点成员和一个递归成员。 可以定义多个定位点成员和递归成员；但必须将所有定位点成员查询定义置于第一个递归成员定义之前。 所有 CTE 查询定义都是定位点成员，但它们引用 CTE 本身时除外。

注意

定位点成员必须与以下集合运算符之一结合使用：UNION ALL、UNION、INTERSECT 或 EXCEPT。 在最后一个定位点成员和第一个递归成员之间，以及组合多个递归成员时，只能使用 UNION ALL 集合运算符

WITH locs(id,name,parent,loclevel)AS(-----基本语句 SELECT id,name,parent,0 AS loclevel FROM tb_locWHERE parent IS NULLUNION ALL---递归语句SELECT l.id,l.name,l.parent,loclevel+1 FROM tb_loc l INNER JOIN locs p ON l.parent=p.id --递归调用)SELECT * FROM locsOPTION(MAXRECURSION 2)--指定最大递归次数为2，可以不指定