SqlServer共用表达式（CTE）With As

共用表表达式（CTE）可以看成是一个临时的结果集，可以再SELECT，INSERT，UPDATE，DELETE，MARGE语句中多次引用。

一好处：使用共用表表达式可以让语句更加清晰简练。

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

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

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

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

二定义：公用表达式的定义非常简单，只包含三部分：

1.   公用表表达式的名字（在WITH之后）
2.   所涉及的列名（可选）
3.   一个SELECT语句(紧跟AS之后)

    在MSDN中的原型：

WITH expression_name [ ( column_name [,...n] ) ] 

AS 

( CTE_query_definition ) 

 按照是否递归，可以将公用表（CTE）表达式分为递归公用表表达式和非递归公用表表达式.

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

非递归公用表表达式（CTE）是查询结果仅仅一次性返回一个结果集用于外部查询调用。并不在其定义的语句中调用其自身的CTE

   非递归公用表表达式（CTE）的使用方式和视图以及子查询一致

	WITH cte_Test AS
    (
	SELECT * FROM dbo.SysOrganization
	)
	SELECT * FROM cte_Test

 公用表表达式的好处之一是可以在接下来一条语句中多次引用:

只能接下来一条使用:

由于CTE只能在接下来一条语句中使用，因此，当需要接下来的一条语句中引用多个CTE时，可以定义多个，中间用逗号分隔：

 

递归公用表表达式(CTE)

递归公用表表达式很像派生表（Derived Tables ），指的是在CTE内的语句中调用其自身的CTE.与派生表不同的是，CTE可以在一次定义多次进行派生递归.对于递归的概念，是指一个函数或是过程直接或者间接的调用其自身，递归的简单概念图如下:

 

对于递归公用表达式来说，实现原理也是相同的，同样需要在语句中定义两部分：

•    基本语句
•    递归语句

   在SQL这两部分通过UNION ALL连接结果集进行返回:

 

 

当然，越强大的力量，就需要被约束.如果使用不当的话，递归CTE可能会出现无限递归。从而大量消耗SQL Server的服务器资源.因此，SQL Server提供了OPTION选项，可以设定最大的递归次数:

   还是上面那个语句，限制了递归次数:

 

 

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_query_definition 中使用以下子句：

（1）COMPUTE 或 COMPUTE BY

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

（3）INTO

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

（5）FOR XML

（6）FOR BROWSE

 4.如果将 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所示：

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)

 

比较复杂的例子：

	;WITH v_targetdetail(ID,ParentID,TargetID,TargetName,
	TargetGroupID,ConversionValue,ToTargetID,IsCalculated,IsLeaf,
	IDFullPath,IsDisplay,Unit,TargetLevelID,TargetType) AS (
	-- DECLARE @accountGroupID NVARCHAR(38)='ac11e71148564dd3a28d07142883ab99'
		SELECT p.ID
		    --父级ID
			,p.ParentID
			--实际指标ID
			,ISNULL(p.TargetID,p.ID) TargetID
			--名称
			,p.Name TargetName
			--指标组ID
			,p.GroupID TargetGroupID
			--转换值
			,p.ConversionValue
			--目标指标ID
			,p.ToTargetID
			--是否计算
			,p.IsCalculated
			--是否是叶子
			,p.IsLeaf
			--ID值
			,CAST(p.ID AS NVARCHAR(MAX)) IDFullPath
			--是否显示
			,p.IsDisplay
			--	单位
			,p.Unit
			--级别
			,p.LevelID
			--指标类型
			,p.TargetType
			--指标组表
		FROM ToBusinessTargetGroupDetail2(NOLOCK) p
		--父级ID为空  是否上报
		WHERE (p.ParentID IS NULL OR p.IsReported=0) AND p.GroupID=@accountGroupID--'ac11e71148564dd3a28d07142883ab99'--  


	--全部关联
	UNION ALL
	--递归语句
	SELECT 
	         p.ID
			,p.ParentID
			,ISNULL(p.TargetID,p.ID) TargetID
			,p.Name TargetName
			,p1.TargetGroupID
			,p.ConversionValue
			,p.ToTargetID
			,p.IsCalculated
			,p.IsLeaf
			,CAST(p1.IDFullPath+'.'+p.ID AS NVARCHAR(MAX))IDFullPath
			,p.IsDisplay
			,p.Unit
			,p.LevelID
			,p.TargetType
	
	 FROM ToBusinessTargetGroupDetail2(NOLOCK) p
	 --第一次查询的表  p1为 第一次汇总的值    p为指标详细表   得到的指表组ID==原表的指标组ID
	 INNER JOIN v_targetdetail p1 ON p1.ID=p.ParentID AND p1.TargetGroupID=p.GroupID  
	 --已经上报的
	 WHERE p.IsReported=1
	)

	





	--第二次
	,v_taregetsummaryref(TargetDetailID,pTargetID,pTargetName,sTargetID,
	sTargetName,ConversionValue,pToTargetID,sIsLeaf,
	TargetLevelID,Unit,TargetType,TargetOrderID) AS (
		SELECT p.ID
			,p.TargetID pTargetID
			,p.TargetName pTargetName
			,p1.TargetID sTargetID
			,p1.TargetName sTargetName
			,p.ConversionValue
			--,p1.ConversionValue
			,p.ToTargetID
			,p1.IsLeaf
			,p.TargetLevelID
			,p.Unit
			,p.TargetType
			,NULL
			--第一次
		FROM v_targetdetail p
		INNER JOIN v_targetdetail p1 ON p1.TargetGroupID=p.TargetGroupID AND p1.IDFullPath LIKE p.IDFullPath+'%'
		WHERE p.IsDisplay=1
		)

		--插入到临时表
	INSERT INTO #t_target(TargetDetailID,pTargetID,pTargetName,sTargetID,sTargetName,ConversionValue,pToTargetID,TargetOrderID,TargetLevelID,Unit,TargetType,isleaf)
	--从最终的表里面查询结果出来
	SELECT TargetDetailID,pTargetID,pTargetName,sTargetID,sTargetName,
	ConversionValue,pToTargetID,TargetOrderID,TargetLevelID,
	--第二次递归的表
	Unit,TargetType, sIsLeaf FROM v_taregetsummaryref
	UNION ALL
	SELECT p1.TargetDetailID
		,p1.pTargetID
		,p1.pTargetName
		,p2.ID sTargetID
		,p2.Name sTargetName
		,p1.ConversionValue
		,p1.pToTargetID
		,p1.TargetOrderID
		,p1.TargetLevelID+p2.LevelID
		,p1.Unit
		,10
		,p2.IsLeaf
		--科目表
	FROM ToFinanceAccount(NOLOCK) p
	--最终的结构表   最终结果表ID ==科目表的ID    最终结果表 是叶子结点的数据
	INNER JOIN v_taregetsummaryref p1  WITH(NOLOCK)  ON  p1.sTargetID=p.ID AND p1.sIsLeaf=1
	--科目表   名称全路径 和没有删除的数据
	INNER JOIN dbo.ToFinanceAccount  p2 WITH(NOLOCK) ON p2.FullPath LIKE p.FullPath+'\%' AND ISNULL(p2.IsDelete,0)=0
	--没有删除的数据
	WHERE ISNULL(p.IsDelete,0)=0
	  --科目表中不是叶节点的数据
		AND p.IsLeaf!=1