[求例子]2005 CTE的用法
看文档看不下，太规范了
求2005 CTE的经典例子
如果有稍详细的注释就更好了
顺便小散100吧
[解决办法]
看联机帮助，很清楚。。
[解决办法]
CTE 通用表表达式概念：Common Table Expression，简称CTE，中文可以叫做，通用表表达式．用处：处理以前版本中SQL不好现实，不好理解，复杂的查询问题．比如：分页，递归查询．．．基本用法：WITH <name of your CTE>(<column names>)AS(<actual query>)SELECT * FROM <name of your CTE>示例一（基本用法）：with MyCTE(ID, Name)as( select EmployeeID as ID, FirstName + ' ' + LastName as Name from HumanResources.vEmployee)select * from MyCTE示例二（分页）：with MyCTE(ID, Name, RowID)as( select EmployeeID as ID, FirstName + ' ' + LastName as Name, Row_Number() over (order by EmployeeID) as RowID from HumanResources.vEmployee)select * from MyCTE where RowID between 1 and 10示例三（关联CTE）:with OrderCountCTE(SalesPersonID, OrderCount)as( select SalesPersonID, count(1) from Sales.SalesOrderHeader where SalesPersonID is not null group by SalesPersonID)select sp.SalesPersonID, sp.SalesYTD, cte.OrderCountfrom OrderCountCTE cte inner join Sales.SalesPerson spon cte.SalesPersonID = sp.SalesPersonID order by 3示例四（使用CTE的删除）：CREATE TABLE Products ( Product_ID int NOT NULL, Product_Name varchar (25), Price money NULL,CONSTRAINT PK_Products PRIMARY KEY NONCLUSTERED (Product_ID))GOINSERT INTO Products (Product_ID, Product_Name, Price) VALUES (1, 'Widgets', 25)INSERT INTO Products (Product_ID, Product_Name, Price) VALUES (2, 'Gadgets', 50)INSERT INTO Products (Product_ID, Product_Name, Price) VALUES (3, 'Thingies', 75)INSERT INTO Products (Product_ID, Product_Name, Price) VALUES (4, 'Whoozits', 90)INSERT INTO Products (Product_ID, Product_Name, Price) VALUES (5, 'Whatzits', 5)INSERT INTO Products (Product_ID, Product_Name, Price) VALUES (6, 'Gizmos', 15)INSERT INTO Products (Product_ID, Product_Name, Price) VALUES (7, 'Widgets', 24)INSERT INTO Products (Product_ID, Product_Name, Price) VALUES (8, 'Gizmos', 36)INSERT INTO Products (Product_ID, Product_Name, Price) VALUES (9, 'Gizmos', 36)GO--==================Delete duplicate products=============================with DuplicateProdCTEas(select Min(Product_ID) as Product_ID, Product_Name from Products group by Product_Name having count(1) >1)delete Products from Products p join DuplicateProdCTE cte on cte.Product_Name = p.Product_Name and p.Product_ID > cte.Product_ID示例五（递归查询）：CREATE TABLE Employee_Tree (Employee_NM nvarchar(50), Employee_ID int PRIMARY KEY, ReportsTo int)--insert some data, build a reporting treeINSERT INTO Employee_Tree VALUES('Richard', 1, NULL)INSERT INTO Employee_Tree VALUES('Stephen', 2, 1)INSERT INTO Employee_Tree VALUES('Clemens', 3, 2)INSERT INTO Employee_Tree VALUES('Malek', 4, 2)INSERT INTO Employee_Tree VALUES('Goksin', 5, 4)INSERT INTO Employee_Tree VALUES('Kimberly', 6, 1)INSERT INTO Employee_Tree VALUES('Ramesh', 7, 5)--with MyCTEas( select Employee_ID, Employee_NM, -1 as ReportsTo, 0 as SubLevel from Employee_Tree where ReportsTo is null --root node union all select e.Employee_ID, e.Employee_NM, e.ReportsTo, SubLevel +1 from Employee_Tree e, MyCTE where e.ReportsTo = MyCTE.Employee_ID) --select * from MyCTEselect MyCTE.Employee_NM as emp , MyCTE.SubLevel, e.Employee_NM as boss from MyCTE left join Employee_Tree e on MyCTE.ReportsTo = e.Employee_ID--OPTION(MAXRECURSION 3) --error--OPTION(MAXRECURSION 4) --okwhere SubLevel < 4注意：OPTION(MAXRECURSION 4)用来设置递归时查找的层数，默认是100，如果超过默认或指定的，则会报错．通常我们用一个层数列来过滤指定的层． 本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/htl258/archive/2009/04/25/4115298.aspx
[解决办法]
--联机示例A. 创建一个简单公用表表达式以下示例显示直接向 Adventure Works Cycles 的每个经理报告的雇员的数目。 USE AdventureWorks2008R2;GOWITH Sales_CTE (SalesPersonID, NumberOfOrders)AS( SELECT SalesPersonID, COUNT(*) FROM Sales.SalesOrderHeader WHERE SalesPersonID IS NOT NULL GROUP BY SalesPersonID)SELECT SalesPersonID, NumberOfOrders FROM Sales_CTE ORDER BY SalesPersonID;GO B. 使用公用表表达式来限制次数和报告平均数以下示例显示向经理报告的雇员的平均数。 WITH Sales_CTE (SalesPersonID, NumberOfOrders)AS( SELECT SalesPersonID, COUNT(*) FROM Sales.SalesOrderHeader WHERE SalesPersonID IS NOT NULL GROUP BY SalesPersonID)SELECT AVG(NumberOfOrders) AS [Average Sales Per Person]FROM Sales_CTE;GO C. 多次引用同一个公用表表达式以下示例显示 SalesOrderHeader 表中每个销售人员的销售订单的总数和最近的销售订单的日期。 USE AdventureWorks2008R2;GO-- Define the CTE expression name and column list.WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)AS-- Define the CTE query.( SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear FROM Sales.SalesOrderHeader WHERE SalesPersonID IS NOT NULL)-- Define the outer query referencing the CTE name.SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYearFROM Sales_CTEGROUP BY SalesYear, SalesPersonIDORDER BY SalesPersonID, SalesYear;GO 使用递归公用表表达式显示递归的多个级别。以下示例显示经理以及向经理报告的雇员的层次列表。 USE AdventureWorks2008R2;GOWITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS ( SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel FROM HumanResources.Employee WHERE ManagerID IS NULL UNION ALL SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1 FROM HumanResources.Employee e INNER JOIN DirectReports d ON e.ManagerID = d.EmployeeID )SELECT ManagerID, EmployeeID, EmployeeLevel FROM DirectReports ;GO E. 使用递归公用表表达式显示递归的两个级别。以下示例显示经理以及向经理报告的雇员。将返回的级别数目被限制为两个。 USE AdventureWorks2008R2;GOWITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS ( SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel FROM HumanResources.Employee WHERE ManagerID IS NULL UNION ALL SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1 FROM HumanResources.Employee e INNER JOIN DirectReports d ON e.ManagerID = d.EmployeeID )SELECT ManagerID, EmployeeID, EmployeeLevel FROM DirectReports WHERE EmployeeLevel <= 2 ;GO F. 使用递归公用表表达式显示层次列表以下示例在示例 C 的基础上添加经理和雇员的名称，以及他们各自的头衔。通过缩进各个级别，突出显示经理和雇员的层次结构。 USE AdventureWorks2008R2;GOWITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)AS (SELECT CONVERT(varchar(255), c.FirstName + ' ' + c.LastName), e.JobTitle, e.EmployeeID, 1, CONVERT(varchar(255), c.FirstName + ' ' + c.LastName) FROM HumanResources.Employee AS e JOIN Person.Contact AS c ON e.ContactID = c.ContactID WHERE e.ManagerID IS NULL UNION ALL SELECT CONVERT(varchar(255), REPLICATE ('| ' , EmployeeLevel) + c.FirstName + ' ' + c.LastName), e.JobTitle, e.EmployeeID, EmployeeLevel + 1, CONVERT (varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' + LastName) FROM HumanResources.Employee as e JOIN Person.Contact AS c ON e.ContactID = c.ContactID JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID )SELECT EmployeeID, Name, Title, EmployeeLevelFROM DirectReports ORDER BY Sort;GO G. 使用 MAXRECURSION 取消一条语句可以使用 MAXRECURSION 来防止不合理的递归 CTE 进入无限循环。下面的示例特意创建了一个无限循环，然后使用 MAXRECURSION 提示将递归级别限制为两级。 USE AdventureWorks2008R2;GO--Creates an infinite loop.WITH cte (CustomerID, PersonID, StoreID) AS( SELECT CustomerID, PersonID, StoreID FROM Sales.Customer WHERE PersonID IS NOT NULL UNION ALL SELECT cte.CustomerID, cte.PersonID, cte.StoreID FROM cte JOIN Sales.Customer AS c ON cte.PersonID = c.CustomerID)--Uses MAXRECURSION to limit the recursive levels to 2.SELECT CustomerID, PersonID, StoreIDFROM cteOPTION (MAXRECURSION 2);GO 在更正代码错误之后，就不再需要 MAXRECURSION。以下示例显示了更正后的代码。 USE AdventureWorks2008R2;GOWITH cte (EmployeeID, ManagerID, Title)AS( SELECT EmployeeID, ManagerID, JobTitle FROM HumanResources.Employee WHERE ManagerID IS NOT NULL UNION ALL SELECT e.EmployeeID, e.ManagerID, e.JobTitle FROM HumanResources.Employee AS e JOIN cte ON e.ManagerID = cte.EmployeeID)SELECT EmployeeID, ManagerID, TitleFROM cte;GO H. 使用公用表表达式来有选择地执行 SELECT 语句中的递归操作以下示例显示了为 ProductAssemblyID = 800 生产自行车所需的产品装配和部件层次结构。 USE AdventureWorks2008R2;GOWITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS( SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty, b.EndDate, 0 AS ComponentLevel FROM Production.BillOfMaterials AS b WHERE b.ProductAssemblyID = 800 AND b.EndDate IS NULL UNION ALL SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty, bom.EndDate, ComponentLevel + 1 FROM Production.BillOfMaterials AS bom INNER JOIN Parts AS p ON bom.ProductAssemblyID = p.ComponentID AND bom.EndDate IS NULL)SELECT AssemblyID, ComponentID, Name, PerAssemblyQty, EndDate, ComponentLevel FROM Parts AS p INNER JOIN Production.Product AS pr ON p.ComponentID = pr.ProductIDORDER BY ComponentLevel, AssemblyID, ComponentID;GO I. 在 UPDATE 语句中使用递归 CTE以下示例将直接或间接向 ManagerID 12 报告的所有雇员的 VacationHours 值增加 25%。公用表表达式将返回直接向 ManagerID 12 报告的雇员以及直接向这些雇员报告的雇员等的层次列表。只修改公用表表达式所返回的行。 USE AdventureWorks2008R2;GOWITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS( SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty, b.EndDate, 0 AS ComponentLevel FROM Production.BillOfMaterials AS b WHERE b.ProductAssemblyID = 800 AND b.EndDate IS NULL UNION ALL SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty, bom.EndDate, ComponentLevel + 1 FROM Production.BillOfMaterials AS bom INNER JOIN Parts AS p ON bom.ProductAssemblyID = p.ComponentID AND bom.EndDate IS NULL)UPDATE Production.BillOfMaterialsSET PerAssemblyQty = c.PerAssemblyQty * 2FROM Production.BillOfMaterials AS cJOIN Parts AS d ON c.ProductAssemblyID = d.AssemblyIDWHERE d.ComponentLevel = 0; 使用多个定位点和递归成员以下示例使用多个定位点和递归成员来返回指定的人的所有祖先。创建了一个表，并在表中插入值，以建立由递归 CTE 返回的宗谱。 -- Genealogy tableIF OBJECT_ID('dbo.Person','U') IS NOT NULL DROP TABLE dbo.Person;GOCREATE TABLE dbo.Person(ID int, Name varchar(30), Mother int, Father int);GOINSERT dbo.Person VALUES(1, 'Sue', NULL, NULL) ,(2, 'Ed', NULL, NULL) ,(3, 'Emma', 1, 2) ,(4, 'Jack', 1, 2) ,(5, 'Jane', NULL, NULL) ,(6, 'Bonnie', 5, 4) ,(7, 'Bill', 5, 4);GO-- Create the recursive CTE to find all of Bonnie's ancestors.WITH Generation (ID) AS(-- First anchor member returns Bonnie's mother. SELECT Mother FROM dbo.Person WHERE Name = 'Bonnie'UNION-- Second anchor member returns Bonnie's father. SELECT Father FROM dbo.Person WHERE Name = 'Bonnie'UNION ALL-- First recursive member returns male ancestors of the previous generation. SELECT Person.Father FROM Generation, Person WHERE Generation.ID=Person.IDUNION ALL-- Second recursive member returns female ancestors of the previous generation. SELECT Person.Mother FROM Generation, dbo.Person WHERE Generation.ID=Person.ID)SELECT Person.ID, Person.Name, Person.Mother, Person.FatherFROM Generation, dbo.PersonWHERE Generation.ID = Person.ID;GO
[解决办法]
--利用cte实现递归--> 测试数据：[tb]if object_id('[tb]') is not null drop table [tb]create table [tb]([id] int,[col1] varchar(8),[col2] int)insert [tb]select 1,'河北省',0 union allselect 2,'邢台市',1 union allselect 3,'石家庄市',1 union allselect 4,'张家口市',1 union allselect 5,'南宫',2 union allselect 6,'坝上',4 union allselect 7,'任县',2 union allselect 8,'清河',2 union allselect 9,'河南省',0 union allselect 10,'新乡市',9 union allselect 11,'aaa',10 union allselect 12,'bbb',10;with t as(select * from [tb] where col1='河北省'union allselect a.* from [tb] a ,t where a.col2=t.id)select * from t/*id col1 col2----------- -------- -----------1 河北省 02 邢台市 13 石家庄市 14 张家口市 16 坝上 45 南宫 27 任县 28 清河 2(8 行受影响)*/
[解决办法]
下面的准则适用于定义递归公用表表达式： 递归 CTE 定义至少必须包含两个 CTE 查询定义，一个定位点成员和一个递归成员。可以定义多个定位点成员和递归成员；但必须将所有定位点成员查询定义置于第一个递归成员定义之前。所有 CTE 查询定义都是定位点成员，但它们引用 CTE 本身时除外。 Anchor members must be combined by one of these set operators: UNION ALL, UNION, INTERSECT, or EXCEPT.在最后一个定位点成员和第一个递归成员之间，以及组合多个递归成员时，只能使用 UNION ALL 集合运算符。 定位点成员和递归成员中的列数必须一致。递归成员中列的数据类型必须与定位点成员中相应列的数据类型一致。递归成员的 FROM 子句只能引用一次 CTE expression_name。在递归成员的 CTE_query_definition 中不允许出现下列项： SELECT DISTINCTGROUP BYHAVING标量聚合TOPLEFT、RIGHT、OUTER JOIN（允许出现 INNER JOIN）子查询应用于对 CTE_query_definition 中的 CTE 的递归引用的提示。下面的准则适用于使用递归公用表表达式： 无论参与的 SELECT 语句返回的列的为 Null 性如何，递归 CTE 返回的全部列都可以为空。如果递归 CTE 组合不正确，可能会导致无限循环。例如，如果递归成员查询定义对父列和子列返回相同的值，则会造成无限循环。可以使用 MAXRECURSION 提示以及在 INSERT、UPDATE、MERGE、DELETE 或 SELECT 语句的 OPTION 子句中的一个 0 到 32,767 之间的值，来限制特定语句所允许的递归级数，以防止出现无限循环。这样就能够在解决产生循环的代码问题之前控制语句的执行。服务器范围内的默认值是 100。如果指定 0，则没有限制。每一个语句只能指定一个 MAXRECURSION 值。有关详细信息，请参阅查询提示 (Transact-SQL)。不能使用包含递归公用表表达式的视图来更新数据。可以使用 CTE 在查询上定义游标。CTE 是定义游标结果集的 select_statement 参数。递归 CTE 只允许使用快速只进游标和静态（快照）游标。如果在递归 CTE 中指定了其他游标类型，则该类型将转换为静态游标类型。 可以在 CTE 中引用远程服务器中的表。如果在 CTE 的递归成员中引用了远程服务器，那么将为每个远程表创建一个假脱机，这样就可以在本地反复访问这些表。如果为 CTE 查询，Index Spool/Lazy Spool 则显示在查询计划中，并具有额外的 WITH STACK 谓词。这是一种确认正确递归的方法。SQL Server 2008 不允许在 CTE 的递归部分中使用分析和聚合函数。
[解决办法]
cte的最大用途是在递归方面。
[解决办法]
--1.用CTE生成行号 ;WITH t AS ( SELECT 1 AS num UNION ALL SELECT num+1 FROM t WHERE num<100000 ) SELECT * FROM t OPTION(MAXRECURSION 0) 本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/htl258/archive/2009/09/21/4573244.aspx
[解决办法]
学习，也可以用来更新原表
[解决办法]
--生成一个数字表，效率非常高create function dbo.fn_nums(@n as bigint) returns tableasreturn with t1 as (select 1 as c union all select 1), t2 as (select 1 as c from t1 as a,t1 as b), t3 as (select 1 as c from t2 as a,t2 as b), t4 as (select 1 as c from t3 as a,t3 as b), t5 as (select 1 as c from t4 as a,t4 as b), t6 as (select 1 as c from t5 as a,t5 as b), t7 as (select row_number() over(order by c) as n from t6) select n from t7 where n<@n;go--测试select * from dbo.fn_nums(1000)
[解决办法]
--更新或者删除前n跳数据;with t as(select top n from tb order by id desc)update t set .....--ordelete t
[解决办法]
--3.如果CTE的表达式名称与某个数据表或视图重名，则紧跟在该CTE后面的SQL语句使用的仍然是CTE，当然，后面的SQL语句使用的就是数据表或视图了，如下面的SQL语句所示：-- table1是一个实际存在的表with table1 as( select * from persons where age<30)select * from table1 -- 使用了名为table1的公共表表达式select * from table1 -- 使用了名为table1的数据表--4.CTE 可以引用自身，也可以引用在同一 WITH 子句中预先定义的 CTE。不允许前向引用。--5.不能在 CTE_query_definition 中使用以下子句： （1）COMPUTE 或 COMPUTE BY （2）ORDER BY（除非指定了 TOP 子句） （3）INTO （4）带有查询提示的 OPTION 子句 （5）FOR XML （6）FOR BROWSE--6.如果将 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 CountryRegionCodein(select * fromt_tree)
[解决办法]
学习之，看了一下联机帮助，内容很多没看完呢
[解决办法]
我这里有几个简单的实例，看看能给lz一点帮助http://blog.csdn.net/jerryjbiao/archive/2009/11/19/4832182.aspx
[解决办法]
create table #EnterPrise( Department nvarchar(50),--部门名称 ParentDept nvarchar(50),--上级部门 DepartManage nvarchar(30)--部门经理)insert into #EnterPrise select '技术部','总经办','Tom'insert into #EnterPrise select '商务部','总经办','Jeffry'insert into #EnterPrise select '商务一部','商务部','ViVi'insert into #EnterPrise select '商务二部','商务部','Peter'insert into #EnterPrise select '程序组','技术部','GiGi'insert into #EnterPrise select '设计组','技术部','yoyo'insert into #EnterPrise select '专项组','程序组','Yue'insert into #EnterPrise select '总经办','','Boss'--查询部门经理是Tom的下面的部门名称;with hgo as( select *,0 as rank from #EnterPrise where DepartManage='Tom' union all select h.*,h1.rank+1 from #EnterPrise h join hgo h1 on h.ParentDept=h1.Department)select * from hgo/*Department ParentDept DepartManage rank--------------- -------------------- ----------------------- -----------技术部 总经办 Tom 0程序组 技术部 GiGi 1设计组 技术部 yoyo 1专项组 程序组 Yue 2*/--查询部门经理是GiGi的上级部门名称;with hgo as( select *,0 as rank from #EnterPrise where DepartManage='GiGi' union all select h.*,h1.rank+1 from #EnterPrise h join hgo h1 on h.Department=h1.ParentDept)select * from hgo/*Department ParentDept DepartManage rank-------------------- ---------------------- ----------- -----------程序组 技术部 GiGi 0技术部 总经办 Tom 1总经办 Boss 2*/本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/ws_hgo/archive/2010/01/31/5274571.aspx
[解决办法]
学习，可以推荐了
[解决办法]
USE tempdbGO-- 建立演示环境CREATE TABLE Dept( id int PRIMARY KEY, parent_id int, name nvarchar(20))INSERT DeptSELECT 0, 0, N'<全部>' UNION ALLSELECT 1, 0, N'财务部' UNION ALLSELECT 2, 0, N'行政部' UNION ALLSELECT 3, 0, N'业务部' UNION ALLSELECT 4, 0, N'业务部' UNION ALLSELECT 5, 4, N'销售部' UNION ALLSELECT 6, 4, N'MIS' UNION ALLSELECT 7, 6, N'UI' UNION ALLSELECT 8, 6, N'软件开发' UNION ALLSELECT 9, 8, N'内部开发'GO-- 查询指定部门下面的所有部门DECLARE @Dept_name nvarchar(20)SET @Dept_name = N'MIS';WITHDEPTS AS( -- 定位点成员 SELECT * FROM Dept WHERE name = @Dept_name UNION ALL -- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归 SELECT A.* FROM Dept A, DEPTS B WHERE A.parent_id = B.id)SELECT * FROM DEPTSGO-- 删除演示环境DROP TABLE Dept----CTE的综合应用USE tempdbGO-- 建立演示环境CREATE TABLE Dept( id int PRIMARY KEY, parent_id int, name nvarchar(20))INSERT DeptSELECT 0, 0, N'<全部>' UNION ALLSELECT 1, 0, N'财务部' UNION ALLSELECT 2, 0, N'行政部' UNION ALLSELECT 3, 0, N'业务部' UNION ALLSELECT 4, 0, N'业务部' UNION ALLSELECT 5, 4, N'销售部' UNION ALLSELECT 6, 4, N'MIS' UNION ALLSELECT 7, 6, N'UI' UNION ALLSELECT 8, 6, N'软件开发' UNION ALLSELECT 9, 8, N'内部开发'GO-- 查询指定部门下面的所有部门, 并汇总各部门的下级部门数DECLARE @Dept_name nvarchar(20)SET @Dept_name = N'MIS';WITHDEPTS AS( -- 查询指定部门及其下的所有子部门 -- 定位点成员 SELECT * FROM Dept WHERE name = @Dept_name UNION ALL -- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归 SELECT A.* FROM Dept A, DEPTS B WHERE A.parent_id = B.id),DEPTCHILD AS( -- 引用第1个CTE,查询其每条记录对应的部门下的所有子部门 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( -- 引用第2个CTE, 汇总得到各部门下的子部门数 SELECT Dept_id, Cnt = COUNT(*) FROM DEPTCHILD GROUP BY Dept_id)SELECT -- JOIN第1,3个CTE,得到最终的查询结果 D.*, ChildDeptCount = ISNULL(DS.Cnt, 0)FROM DEPTS D LEFT JOIN DEPTCHILDCNT DS ON D.id = DS.Dept_idGO-- 删除演示环境DROP TABLE Dept本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/fredrickhu/archive/2009/09/19/4569529.aspx
[解决办法]
这个一定要收藏一下
[解决办法]
.....
[解决办法]
这个一定要收藏一下
[解决办法]
路过，学习
[解决办法]
好贴，收藏了，up！
[解决办法]
这么长！！！！！！
[解决办法]
learning......
[解决办法]
[code=SQL]CTE;
这个是SQL2005新增加的,个人比较喜欢。你可以将它理解为一个临时表.它虽然不支持嵌套,但是可以变相嵌套。
WITH t1 as
(select ...from tb),
t2 as
(select ... from t1),
t3 as
(select ....from t2)
select * from t3
下一个CTE可以用上面一个CTE，外部的查询可以访问所有的CTE(一个批处理内).
PS；在CTE的WITH之前的语句必须用;号结束.这是因为WITH在SQL里还有其他作用,避免歧义.
上面我提到了,派生表是不能多次引用了,但是CTE是不受限制的，它可以这么用。
WITH t1 as
(select ...from tb)
select a.* from t1 a join t1 b on .....
CTE也可以用于表的删除更新,因为对CTE的操作是会影响原来的表的。如：
IF OBJECT_ID('dbo.T1') IS NOT NULL
DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1
(
keycol INT NOT NULL PRIMARY KEY CHECK(keycol > 0),
datacol VARCHAR(10) NOT NULL
);
INSERT INTO dbo.T1(keycol, datacol) VALUES(3, 'a');
INSERT INTO dbo.T1(keycol, datacol) VALUES(4, 'b');
INSERT INTO dbo.T1(keycol, datacol) VALUES(6, 'c');
INSERT INTO dbo.T1(keycol, datacol) VALUES(7, 'd');
go
with cte as
(
select *
from T1
where keycol>4
)
delete cte
select * from T1
/*
keycol datacol
----------- ----------
3 a
4 b
*/
不得不提下个人觉得CTE最强大最实用的地方：递归中的应用.
--假设是单继承,我这意思就是一个孩子只有一个父亲
create table #testt(child int,pid int)
insert #testt select
1,null union all select
2,1 union all select
3,1 union all select
4,2 union all select
5,1 union all select
6,2 union all select
7,3 union all select
8,6 union all select
9,8
--查找所有指定孩子的父亲节点
declare @child int
set @child=8
;with cte as
(
select pid from #testt where child=@child
union all
select a.pid from #testt a join cte b on b.pid=a.child
)
select * from cte where pid is not null order by 1
/*
pid
-----------
1
2
6
*/
--查找指定节点所有的子节点
declare @pid int
set @pid=6
;with ctes as
(
select child from #testt where pid=@pid
union all
select a.child from #testt a join ctes b on a.pid=b.child
)
select * from ctes
/*
child
-----------
8
9
*/
我这只是举个最简单的BOM递归中CTE的运用（关于更多应用,以后会再介绍）
这里我介绍下这个递归里面的东西：
它包含2个东西：定位点成员-- select child from #testt where pid=@pid 就是第一条查询语句,
递归成员-- select a.child from #testt a join ctes b on a.pid=b.child 这条就是对CTE本身的引用的语句.\\\
可以这么想,每次对CTE本身的引用就是本次递归的"前一个结果集".CTE直到递归成员返回null的时候结束。
这里还有一个东西:每个CTE是有默认的递归层数的--100次.如果你想设置,甚至取消这个限制,可以这样
;with ctes as
(
select child from #testt where pid=@pid
union all
select a.child from #testt a join ctes b on a.pid=b.child
)
select * from ctes option (maxrecursion 1000)
这里的1000就是嵌套循环的次数上限;如果你想取消限制，设置其为0.
[code]
简单介绍 看http://blog.csdn.net/feixianxxx/archive/2009/10/20/4704774.aspx
递归运用 看http://blog.csdn.net/feixianxxx/archive/2009/11/01/4753783.aspx
------解决方案--------------------
xuexi..........
[解决办法]
路过，up
[解决办法]
up...
[解决办法]
每天回帖即可获得10分可用分！
[解决办法]
学习，也可以用来更新原表
[解决办法]
with cte as ( select name from sys.objects)select * from cte;
[解决办法]
MARK一下