| 网站首页 | 业界新闻 | 小组 | 威客 | 人才 | 下载频道 | 博客 | 代码贴 | 在线编程 | 编程论坛

问题点数：0 回复次数：4

-- 用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，此时

• 5
• 1/1页
• 1

Powered by Discuz, Processed in 0.148334 second(s), 9 queries.