什么叫CTE：
指定临时命名的结果集，这些结果集称为公用表表达式 (CTE)。
语法：
[ WITH <common_table_expression> [ ,...n ] ]
<common_table_expression>::=
expression_name [ ( column_name [ ,...n ] ) ]
AS
( CTE_query_definition )
expression_name :
公用表表达式的有效标识符。
column_name :
在公用表表达式中指定列名
CTE_query_definition :
指定一个其结果集填充公用表表达式的 SELECT 语句
这里主要是学习递归CTE。
这里使用帮助文档的SQL例子。
以下示例使用多个定位点和递归成员来返回指定的人的所有祖先。
家谱结构如图：
-- 创建家谱表并插入数据：
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
--建立递归CTE，找出邦妮(Bonnie)的所以祖先:
WITH Generation (ID) AS
(
-- 第一个定位点：找出Bonnie的母亲
SELECT Mother
FROM Person
WHERE Name = 'Bonnie'
UNION
-- 第二个定位点：找出Bonnie的父亲
SELECT Father
FROM Person
WHERE Name = 'Bonnie'
UNION ALL
-- 第一个递归找出父辈的祖先（男）
SELECT Person.Father
FROM Generation, Person
WHERE Generation.ID=Person.ID
UNION ALL
-- 第二个递归找出父辈的祖先（女）
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
在Word中画图说明,如图：
结果如图Genealogy2：
找出与Sue有血缘关系的子孙：
Sue--->Emma、Jack--->Bill、Bonnie
如图：
--找出与Sue有血缘关系的子孙
WITH Generation1(id) as
(
SELECT ID FROM Person WHERE Name='Sue'
UNION ALL
SELECT A.ID FROM Person A
INNER JOIN Generation1 B ON A.Mother=B.id
UNION ALL
SELECT A.ID FROM Person A
INNER JOIN Generation1 B ON A.Father=B.id
)
SELECT Person.ID, Person.Name, Person.Mother, Person.Father
FROM Generation1, Person
WHERE Generation1.ID = Person.ID;
查询结果图：
