递归实现族系查询

ID Parent  Name
1     0            祖父
2     1            父亲
3     1            叔伯
4     2            自己
5     4            儿子
6     5            孙子
7     2            姐妹
8     3            表亲
9     7            外甥
10   4           女儿
11  10          外孙
12   5            孙女

这是一个族系表Family

1已知任一ID，求上辈族系树（从幼到长顺序），如已知 ID=11, 返回 :
11, 10, 外孙
10, 4, 女儿
4, 2, 自己
2, 1, 父亲
1, 0, 祖父

2、已知任一ID，求其小辈列表， 如已知 ID=4，返回 :
4, 2, 自己
5, 4, 儿子
6, 5, 孙子
12, 5, 孙女
10, 4, 女儿
11, 10, 外孙

方法：应用SQL SERVER2005的CTE（Common Table Expression) 可以实现递归来实现其过程。

WITH ()
AS
(
 --递归运算中所参照得基本数据,一般称为不变成员(Anchor Member  AM)
 SELECT 数据...
 UNION ALL
 --查询语法,参照自己,形成递归,一般称为递归成员(Recursive Member  RM)
 SELECT ... JOIN ...
)
--将结果显示出来
SELECT FROM

CTE的递归演算逻辑如下:
1) 通过不变的成员取得整体基本数据集合
2) 通过CTE的定义，从第N次的数据集合中，取出第N+1次的数据
3) 重复执行第2步，直到N+1次的数据集合是空集合
4) 创建输出的数据，把所有的结果做UNION ALL

 

1 从幼到长

DECLARE @ID INT
SET @ID=4;

WITH Familys (ID,Parent,Name) AS
(
 SELECT ID,Parent,Name FROM Family WHERE ID=@ID
 UNION ALL
 SELECT Family.ID,Family.Parent,Family.Name FROM Familys INNER JOIN Family
  ON Familys.Parent=Family.ID
)
SELECT  * FROM Familys

--结果

4 2 自己
2 1 父亲
1 0 祖父

2 从长到幼

DECLARE @ID INT
SET @ID=4;

WITH Familys (ID,Parent,Name) AS
(
 SELECT ID,Parent,Name FROM Family WHERE ID=@ID
 UNION ALL
 SELECT Family.ID,Family.Parent,Family.Name FROM Familys INNER JOIN Family
  ON Familys.ID=Family.Parent
)
SELECT  * FROM Familys

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15042150/viewspace-497742/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15042150/viewspace-497742/