编程论坛 → SQL Server论坛

玩玩SQL的递归

mywisdom88 发布于 2022-07-15 17:08， 1679 次点击

-- 用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

只有本站会员才能查看附件，请登录

4 回复

#2

mywisdom882022-07-15 17:11

-- 查 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

只有本站会员才能查看附件，请登录

#3

mywisdom882022-07-15 17:17

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

只有本站会员才能查看附件，请登录

#4

mywisdom882022-07-15 17:19

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

只有本站会员才能查看附件，请登录

#5

mywisdom882022-07-15 17:25

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

1