[求例子]2005 CTE的用法解决方案

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
------解决方案--------------------
SQL code
--联机示例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