-- 用SQL的递归处理家族关系
create table family(fid int,Relation varchar(25),fname varchar(25),pid int)
insert into family values(1,'父亲','A0000001',0)
insert into family values(2,'配偶','B0000001',1)

insert into family values(3,'父子','A0001001',1)
insert into family values(4,'父女','A0001002',1)
insert into family values(5,'父子','A0001003',1)

insert into family values(6,'配偶','C0000001',3)
insert into family values(7,'父子','A0002001',3)
insert into family values(8,'父女','A0002002',3)

insert into family values(9,'配偶','D0000001',5)
insert into family values(10,'父女','A0003001',5)
insert into family values(11,'父子','A0003002',5)

insert into family values(12,'配偶','E0000001',4)

insert into family values(13,'配偶','F0000001',11)
insert into family values(14,'父子','A0004001',11)
insert into family values(15,'父女','A0004002',11)
insert into family values(16,'父女','A0004003',11)
select * from family

-- 查 Fid=5的，祖先和子孙，
with fm1(fid,Relation,fname,pid) -- 查祖先递归
as
(select fid,Relation,fname,pid from family where fid=5
 union all
 select b1.* from family b1 inner join fm1 on fm1.pid = b1.fid) , -- 别忘记这个逗号，多个CTE，中间用逗号

fm2(fid,Relation,fname,pid) -- 查子孙递归
as
(select fid,Relation,fname,pid from family where fid=5
 union all
 select b2.* from family b2 inner join fm2 on b2.pid = fm2.fid and b2.Relation<>'配偶')

select * from fm1
union
select * from fm2
union
select fm.* from family fm inner join fm1 on fm.pid=fm1.fid and fm.Relation='配偶'
union
select fm.* from family fm inner join fm2 on fm.pid=fm2.fid and fm.Relation='配偶'
order by pid

-- 查 Fid=11，祖先和子孙，只改变2楼的 fid=11，此时，FID=10的没了，也就是FID=11的姑姑不显示了。因为姑姑不属于11的祖先

-- 查 Fid=3，祖先和子孙，只改变2楼的 fid=3，此时

原来，SQL的递归这么简单的，要SQL2005版本以上的才有这个功能，CTE公用表表达式