cte原来还可以这样

[you_tube]

原来不知道CTE还可以这样，以前很少用到，迭代相加时基本用master..spt_values代替，此数据最大值一般只能用到255，
今天发现CTE也可以这样

--测试环境
--Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--	Jul  9 2008 14:43:34 
--	Copyright (c) 1988-2008 Microsoft Corporation
--	Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3, v.5938)

--测试TSQL
with leno (r)
as 
(
	select 1 r 
	union all
	select r + 1 from leno where r <5
)
select * from leno

--测试结果
r
-----------
1
2
3
4
5

(5 行受影响)

这样很多用到master..spt_values表的时候基本不用了，可以通过CTE代替，而且还不受数据大小的限制
如：

SELECT SUBSTRING(@Lctext2,number,1) as value
from master..spt_values
where type='p' and number <=LEN(@Lctext2)and CHARINDEX(SUBSTRING(@Lctext2,number,1),@Lctext1)>0

此代码可以用CTE代替，不再受number字段的值是从1至255这样的值限制了,呵，，，

[cailee]

难道是沙发？

[永生天地]

db.

[sql_sf]

学习.

[Zoezs]

cte本来最好的体现就是在递归啊。

[guguda2008]

不对啊SPT_VALUES是0-2047啊
SELECT NUMBER FROM MASTER..SPT_VALUES WHERE TYPE='P'

[cailee]

2008没有打SP1的补丁。

[sql_lover]

2005向上，spt_value可有2048个取值

[you_tube]

[Quote=引用 7 楼 sql_lover 的回复:]

2005向上，spt_value可有2048个取值
[/Quote]
2008是0~2047的取值范围，
2005没这么多吧，没测试过，，有环境的兄弟可以测试一下

[htl258_Tony]

还有好多花样可以变

[you_tube]

[Quote=引用 9 楼 htl258 的回复:]

还有好多花样可以变
[/Quote]
嗯嗯

[cailee]

[Quote=引用 8 楼 you_tube 的回复:]
引用 7 楼 sql_lover 的回复:

2005向上，spt_value可有2048个取值

2008是0~2047的取值范围，
2005没这么多吧，没测试过，，有环境的兄弟可以测试一下
[/Quote]
我刚才测了。2005和2008都是0-2047

[SQL77]

[Quote=引用 8 楼 you_tube 的回复:]
引用 7 楼 sql_lover 的回复:

2005向上，spt_value可有2048个取值

2008是0~2047的取值范围，
2005没这么多吧，没测试过，，有环境的兄弟可以测试一下
[/Quote]
有这么多,你这样的写法也了解过,呵呵,不过一般都不这样写,技术内幕讲得比较多,
利用CTE来产生数字辅助表

[mugua604]

消息 530，级别 16，状态 1，第 1 行
语句被终止。完成执行语句前已用完最大递归 100。

这个什么原因？

[cailee]

[Quote=引用 13 楼 mugua604 的回复:]
SQL code

消息 530，级别 16，状态 1，第 1 行
语句被终止。完成执行语句前已用完最大递归 100。



这个什么原因？
[/Quote]
CTE有个默认的最大递归次数吧？默认限制好像是100.

[tbb_520]

[Quote=引用 14 楼 cailee 的回复:]
引用 13 楼 mugua604 的回复:
SQL code

消息 530，级别 16，状态 1，第 1 行
语句被终止。完成执行语句前已用完最大递归 100。



这个什么原因？

CTE有个默认的最大递归次数吧？默认限制好像是100.
[/Quote]
限制有个命令可以取消的。不过我忘了。。
我一般是自己建立一个数字辅助表，技术内幕中也是那样建议的

[ShenLiang2025]

解决方案：

with leno (r)
as 
(
    select 1 r 
    union all
    select r + 1 from leno where r < 3000
)

select COUNT(*) from leno
OPTION ( MAXRECURSION 0 )

-----------
3000

(1 row(s) affected)

[youyou2404]

jf.

[橡胶轮胎行业数字化高总]

楼主应该改一下，普及一下CTE的用法！

[橡胶轮胎行业数字化高总]

/*****************************************************************
*CTE应用示例
*版权所有：Gaojier 引用请注明
******************************************************************/
--一、语法
--[ WITH <common_table_expression> [ ,...n ] ]
--<common_table_expression>::=
--        expression_name [ ( column_name [ ,...n ] ) ]
--    AS
--        ( CTE_query_definition )
--二、参数
-- expression_name 
--公用表表达式的有效标识符。 expression_name 必须与在同一 WITH <common_table_expression> 
--子句中定义的任何其他公用表表达式的名称不同，但 expression_name 可以与基表或基视图的名称相同。
--在查询中对 expression_name 的任何引用都会使用公用表表达式，而不使用基对象。
--column_name 
--在公用表表达式中指定列名。在一个 CTE 定义中不允许出现重复的名称。
--指定的列名数必须与 CTE_query_definition 结果集中列数匹配。
--只有在查询定义中为所有结果列都提供了不同的名称时，列名称列表才是可选的。
--CTE_query_definition 
--指定一个其结果集填充公用表表达式的 SELECT 语句。
--除了 CTE 不能定义另一个 CTE 以外，CTE_query_definition 的 SELECT 语句必须满足与创建视图时相同的要求。
--如果定义了多个 CTE_query_definition，则这些查询定义必须用下列一个集合运算符联接起来：
--UNION ALL、UNION、EXCEPT 或 INTERSECT。 
--三、递归
--伪代码和语义
--递归 CTE 结构必须至少包含一个定位点成员和一个递归成员。
--以下伪代码显示了包含一个定位点成员和一个递归成员的简单递归 CTE 的组件。 
--WITH cte_name ( column_name [,...n] )
--AS
--( 
--CTE_query_definition –- Anchor member is defined.
--UNION ALL
--CTE_query_definition –- Recursive member is defined referencing cte_name.
--)
---- Statement using the CTE
--SELECT *
--FROM cte_name
--递归执行的语义如下：
--将 CTE 表达式拆分为定位点成员和递归成员。
--运行定位点成员，创建第一个调用或基准结果集 (T0)。
--运行递归成员，将 Ti 作为输入，将 Ti+1 作为输出。
--重复步骤 3，直到返回空集。
--返回结果集。这是对 T0 到 Tn 执行 UNION ALL 的结果。
--四、示例
--A.简单的CTE
USE AdventureWorks;
GO
WITH DirReps(ManagerID, DirectReports) AS 
(
    SELECT ManagerID, COUNT(*) 
    FROM HumanResources.Employee AS e
    WHERE ManagerID IS NOT NULL
    GROUP BY ManagerID
)
SELECT ManagerID, DirectReports 
FROM DirReps
ORDER BY ManagerID;
GO
--B.基表覆盖
CREATE TABLE T1(id INT)
INSERT T1 
SELECT 1 UNION ALL
SELECT 2 
CREATE TABLE T2(id INT)
GO
WITH T2 AS(
	SELECT * FROM T1)
DROP tal
SELECT * FROM T2

SELECT * FROM T2 --此处的查询结果已经不再是CTE的内容引用
WITH T3 AS
(
	SELECT * FROM T1
)
SELECT * FROM T3

SELECT * FROM T3 --此时会提示错误

DROP TABLE T1
DROP TABLE T2
--C.递归
CREATE TABLE Dept(
	id INT PRIMARY KEY,
	parent_id INT,
	NAME NVARCHAR(20))
INSERT Dept 
SELECT 0,0,N'青岛软控' UNION ALL
SELECT 1,0,N'财务管理部' UNION ALL
SELECT 2,0,N'总裁办' UNION ALL 
SELECT 3,0,N'市场营销部' UNION ALL 
SELECT 4,0,N'软控研究院' UNION ALL 
SELECT 5,4,N'电子信息研究所' UNION ALL 
SELECT 6,4,N'信息工程研究所' UNION ALL 
SELECT 7,6,N'MES组' UNION ALL 
SELECT 8,6,N'控制开发组' UNION ALL 
SELECT 9,7,N'数据库开发' 
GO
--查询指定部门下面的所有部门
DECLARE @DeptName NVARCHAR(20)
SET @DeptName=N'信息工程研究所'
;WITH 
Detpts AS
(
	--定位点成员
	SELECT * FROM Dept
	WHERE NAME=@DeptName
	UNION ALL
	SELECT A.*
	FROM Dept A,Detpts B
	WHERE A.parent_id=B.id
)
SELECT * FROM Detpts
DROP TABLE Dept

--D.综合应用
--示例要求：查询指定部门下面的所有部门，同时汇总各部门的下级部门的数来
DECLARE @DeptName NVARCHAR(20)
SET @DeptName=N'信息工程研究所'
;WITH
DEPTS AS
(
	SELECT * FROM Dept
	WHERE [NAME]=@DeptName
	UNION ALL
	SELECT A.* 
	FROM Dept A,DEPTS B
	WHERE A.parent_id=B.id
),
DEPTCHILD AS
(
	SELECT Dept_id=P.id,C.id,C.parent_id 
	FROM DEPTS P,Dept C
	WHERE P.id=C.parent_id
	UNION ALL 
	SELECT P.Dept_id,C.id,C.parent_id
	FROM DEPTCHILD P,Dept C
	WHERE p.id=C.parent_id
),
DEPTCHILDCNT AS
(
	SELECT Dept_id,Cnt=COUNT(*)
	FROM DEPTCHILD
	GROUP BY Dept_id
)
SELECT D.*,ChildDeptCnt=ISNULL(DS.Cnt,0)
FROM DEPTS D LEFT JOIN
	 DEPTCHILDCNT DS ON D.id=DS.Dept_id

--总结：递归经常用于树形菜单、产品结构、组织结构类应用中。