CTE知识问答十

Question

J. Using multiple anchor and recursive members
The following example uses multiple anchor and recursive members to return all the ancestors of a specified person. A table is created and values inserted to establish the family genealogy returned by the recursive CTE.-- Genealogy table
IF OBJECT_ID('Person','U') IS NOT NULL DROP TABLE Person;
GO
CREATE TABLE Person(ID int, Name varchar(30), Mother int, Father int);
GO
INSERT Person VALUES(1, 'Sue', NULL, NULL);
INSERT Person VALUES(2, 'Ed', NULL, NULL);
INSERT Person VALUES(3, 'Emma', 1, 2);
INSERT Person VALUES(4, 'Jack', 1, 2);
INSERT Person VALUES(5, 'Jane', NULL, NULL);
INSERT Person VALUES(6, 'Bonnie', 5, 4);
INSERT Person VALUES(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 Person
    WHERE Name = 'Bonnie'
UNION
-- Second anchor member returns Bonnie's father.
    SELECT Father 
    FROM 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.ID
UNION ALL
-- Second recursive member returns female ancestors of the previous generation.
    SELECT Person.Mother
    FROM Generation, Person
    WHERE Generation.ID=Person.ID
)
SELECT Person.ID, Person.Name, Person.Mother, Person.Father
FROM Generation, Person
WHERE Generation.ID = Person.ID;
GO
问题是这样的，请大家把CTE的十种应用例子的每帖所涉及的例子讲解一番，其应用范围及和其它CTE实例的区别

Answer 1

先查询出Bonnie的父亲、母亲，然后查询Bonnie的父亲的父母、Bonnie的母亲的父母，
也是就查询Bonnie的所有祖先。

Answer 2

--> --> (Roy)生成測試數據
set nocount on ;
if not object_id('Tempdb..#T') is null
    drop table #T
Go
Create table #T([Num] int,[Name] nvarchar(1))
Insert #T
select 1,N'A' union all
select 1,N'A' union all
select 2,N'B' union all
select 2,N'B'go--Delete
;with CTE as
(select row=row_number()over(partition by [Num],[Name] order by (select 1)),[Num],[Name] from #T)
delete CTE where  row>1select * from #T
/*
Num         Name
----------- ----
1           A
2           B
*/--insert
;with CTE as
(select [Num],[Name] from #T)
insert CTE
select * from CTEselect * from #T
/*
Num         Name
----------- ----
1           A
1           A
2           B
2           B
*/--update
;with CTE as
(select row=row_number()over(partition by [Num] order by (select 1)),[Num],[Name] from #T)
update CTE
set [Num]=CTE.rowselect * from #T
/*
Num         Name
----------- ----
1           A
2           A
1           B
2           B
*/

Answer 3

/*一、直接CTE,然后引用全部CTE列,CTE行(最基础的),非递归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;/*二、直接CTE,然后引用全部CTE列,部分CTE行,非递归CTE*/
WITH DirReps (Manager, DirectReports) AS 
(
    SELECT ManagerID, COUNT(*) AS DirectReports
    FROM HumanResources.Employee
    GROUP BY ManagerID
) 
SELECT AVG(DirectReports) AS [Average Number of Direct Reports]
FROM DirReps 
WHERE DirectReports>= 2 ;
GO
/*三、直接CTE,然后引用部分CTE列及外联表的列,属于非递归CTE*/
USE AdventureWorks;
GO
WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)
AS
(
    SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
    FROM Sales.SalesOrderHeader
    GROUP BY SalesPersonID
)
SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
    E.ManagerID, OM.NumberOfOrders, OM.MaxDate
FROM HumanResources.Employee AS E
    JOIN Sales_CTE AS OS
    ON E.EmployeeID = OS.SalesPersonID
    LEFT OUTER JOIN Sales_CTE AS OM
    ON E.ManagerID = OM.SalesPersonID
ORDER BY E.EmployeeID;
GO
/*四、预定义CTE,集合运算符联接起来递归CTE,然后引用全部CTE列,全部CTE行,属于递归CTE*/
USE AdventureWorks;
GO
WITH 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
/*五、预定义CTE,集合运算符联接起来递归CTE,然后引用全部CTE列,部分CTE行,属于递归CTE*/
USE AdventureWorks;
GO
WITH 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
/*六、预定义CTE,集合运算符联接起来递归CTE，然后分组引用部分CTE列,全部CTE行,属于递归CTE*/
USE AdventureWorks;
GO
WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)
AS (SELECT CONVERT(varchar(255), c.FirstName + ' ' + c.LastName),
        e.Title,
        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.Title,
        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, EmployeeLevel
FROM DirectReports 
ORDER BY Sort;
GO/*七、预定义CTE,集合运算符联接起来递归CTE,使用 MAXRECURSION 来防止不合理的递归 CTE 进入无限循环*/
USE AdventureWorks;
GO
--Creates an infinite loop
WITH cte (EmployeeID, ManagerID, Title) as
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT cte.EmployeeID, cte.ManagerID, cte.Title
    FROM cte 
    JOIN  HumanResources.Employee AS e 
        ON cte.ManagerID = e.EmployeeID
)
--Uses MAXRECURSION to limit the recursive levels to 2
SELECT EmployeeID, ManagerID, Title
FROM cte
OPTION (MAXRECURSION 2);/*八、直接CTE,然后引用CTE自身内联*/
USE AdventureWorks;
GO
WITH 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.ProductID
ORDER BY ComponentLevel, AssemblyID, ComponentID;
GO/*九、递归CTE用于UPDATE FROM更新子句*/
USE AdventureWorks;
GO
WITH DirectReports(EmployeeID, NewVacationHours, EmployeeLevel)
AS
(SELECT e.EmployeeID, e.VacationHours, 1
  FROM HumanResources.Employee AS e
  WHERE e.ManagerID = 12
  UNION ALL
  SELECT e.EmployeeID, e.VacationHours, EmployeeLevel + 1
  FROM HumanResources.Employee as e
  JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
UPDATE HumanResources.Employee
SET VacationHours = VacationHours * 1.25
FROM HumanResources.Employee AS e
JOIN DirectReports AS d ON e.EmployeeID = d.EmployeeID;
GO/*十、直接CTE,用CTE列作为条件进行引用*/
-- Genealogy table
IF OBJECT_ID('Person','U') IS NOT NULL DROP TABLE Person;
GO
CREATE TABLE Person(ID int, Name varchar(30), Mother int, Father int);
GO
INSERT Person VALUES(1, 'Sue', NULL, NULL);
INSERT Person VALUES(2, 'Ed', NULL, NULL);
INSERT Person VALUES(3, 'Emma', 1, 2);
INSERT Person VALUES(4, 'Jack', 1, 2);
INSERT Person VALUES(5, 'Jane', NULL, NULL);
INSERT Person VALUES(6, 'Bonnie', 5, 4);
INSERT Person VALUES(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 Person
    WHERE Name = 'Bonnie'
UNION
-- Second anchor member returns Bonnie's father.
    SELECT Father 
    FROM 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.ID
UNION ALL
-- Second recursive member returns female ancestors of the previous generation.
    SELECT Person.Mother
    FROM Generation, Person
    WHERE Generation.ID=Person.ID
)
SELECT Person.ID, Person.Name, Person.Mother, Person.Father
FROM Generation, Person
WHERE Generation.ID = Person.ID;
GO

Answer 4

最后总结一下
其中的不同点主要有一下几个方面：--1、CTE的定义：
--1)直接CTE
WITH Generation (ID) AS (SELECT Mother FROM Person WHERE Name = 'Bonnie')
--2)预定义CTE
WITH cte (EmployeeID, ManagerID, Title) as
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT cte.EmployeeID, cte.ManagerID, cte.Title
    FROM cte 
    JOIN  HumanResources.Employee AS e 
        ON cte.ManagerID = e.EmployeeID
)
--ps:通俗讲就是cte里面用到了cte--2、引用方式：
--1)直接引用：
WITH Generation (ID) AS (SELECT Mother FROM Person WHERE Name = 'Bonnie')
select ID from Generation
--2)复核引用：
WITH Generation (ID) AS (SELECT Mother FROM Person WHERE Name = 'Bonnie')
SELECT Person.ID, Person.Name, Person.Mother, Person.Father
FROM Generation, Person
WHERE Generation.ID = Person.ID;--3、CTE列的使用
--1)引用全部列
WITH Generation (ID) AS (SELECT Mother FROM Person WHERE Name = 'Bonnie')
select ID from Generation
--2)分组显示
WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)
AS (SELECT * from 
    )
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM DirectReports 
ORDER BY Sort;
--3)引用部分列
WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)
AS (SELECT * from 
    )
SELECT sum(EmployeeID) 
FROM DirectReports --4、CTE行的使用，就是有的时候使用where，有的时候没有where
--例如一和二的区别就是多了where条件 和 CTE列的使用 --5、有两个特殊的就是7和9，一个避免死循环的，一个是应用于update from子句

Answer 5

怎么感觉第十个写错了？？？
-- Genealogy table
IF OBJECT_ID('Person','U') IS NOT NULL DROP TABLE Person;
GO
CREATE TABLE Person(ID int, Name varchar(30), Mother int, Father int);
GO
INSERT Person VALUES(1, 'Sue', NULL, NULL);
INSERT Person VALUES(2, 'Ed', NULL, NULL);
INSERT Person VALUES(3, 'Emma', 1, 2);
INSERT Person VALUES(4, 'Jack', 1, 2);
INSERT Person VALUES(5, 'Jane', NULL, NULL);
INSERT Person VALUES(6, 'Bonnie', 5, 4);
INSERT Person VALUES(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 Person
    WHERE Name = 'Bonnie'
UNION
-- Second anchor member returns Bonnie's father.
    SELECT Father 
    FROM 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.ID
UNION ALL
-- Second recursive member returns female ancestors of the previous generation.
    SELECT Person.Mother
    FROM Generation, Person
    WHERE Generation.ID=Person.ID
)
SELECT Person.ID, Person.Name, Person.Mother, Person.Father
FROM Generation, Person
WHERE Generation.ID = Person.ID;GO

Answer 6

怎么感觉第十个写错了？？？ 
-- Genealogy table 
IF OBJECT_ID('Person','U') IS NOT NULL DROP TABLE Person; 
GO 
CREATE TABLE Person(ID int, Name varchar(30), Mother int, Father int); 
GO 
INSERT Person VALUES(1, 'Sue', NULL, NULL); 
INSERT Person VALUES(2, 'Ed', NULL, NULL); 
INSERT Person VALUES(3, 'Emma', 1, 2); 
INSERT Person VALUES(4, 'Jack', 1, 2); 
INSERT Person VALUES(5, 'Jane', NULL, NULL); 
INSERT Person VALUES(6, 'Bonnie', 5, 4); 
INSERT Person VALUES(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 Person 
    WHERE Name = 'Bonnie' 
UNION 
-- Second anchor member returns Bonnie's father. 
    SELECT Father 
    FROM 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.ID 
UNION ALL 
-- Second recursive member returns female ancestors of the previous generation. 
    SELECT Person.Mother 
    FROM Generation, Person 
    WHERE Generation.ID=Person.ID 
) 
SELECT Person.ID, Person.Name, Person.Mother, Person.Father 
FROM Generation, Person 
WHERE Generation.ID = Person.ID;GO 引用 7 楼 dobear_0922 的回复:
先查询出Bonnie的父亲、母亲，然后查询Bonnie的父亲的父母、Bonnie的母亲的父母， 
也是就查询Bonnie的所有祖先。 
 
不对呀，红色部分^

Answer 7

就是红色部分应该用上.parentID吧

Answer 8

山羊(14402542) 16:06:12
-- Genealogy table
IF OBJECT_ID('Person','U') IS NOT NULL DROP TABLE Person;
GO
CREATE TABLE Person(ID int, Name varchar(30), Mother int, Father int);
GO
INSERT Person VALUES(1, 'Sue', NULL, NULL);
INSERT Person VALUES(2, 'Ed', NULL, NULL);
INSERT Person VALUES(3, 'Emma', 1, 2);
INSERT Person VALUES(4, 'Jack', 1, 2);
INSERT Person VALUES(5, 'Jane', NULL, NULL);
INSERT Person VALUES(6, 'Bonnie', 5, 4);
INSERT Person VALUES(7, 'Bill', 5, 4);
GO-- Create the recursive CTE to find all of Bonnie's ancestors.
WITH Generation (ParentID) AS  --这里改了
(
-- First anchor member returns Bonnie's mother.
    SELECT Mother 
    FROM Person
    WHERE Name = 'Bonnie'
UNION
-- Second anchor member returns Bonnie's father.
    SELECT Father 
    FROM Person
    WHERE Name = 'Bonnie'
UNION ALL
-- First recursive member returns male ancestors of the previous generation.
    SELECT Person.Father
    FROM Generation, Person
    WHERE Generation.ParentID=Person.ID --这里改了
UNION ALL
-- Second recursive member returns female ancestors of the previous generation.
    SELECT Person.Mother
    FROM Generation, Person
    WHERE Generation.ParentID=Person.ID --这里改了
)
SELECT Person.ID, Person.Name, Person.Mother, Person.Father
FROM Generation, Person
WHERE Generation.ParentID = Person.ID;--这里改了
GO