每日一学:SQL2000利用函数递归查询

-- 下面用递归方法查询,家族成员名单
if object_id('Family') is not null
drop table Family
go

CREATE TABLE [Family] (
    [ID] [varchar] (10) , --家族成员ID
    [PID] [varchar] (10) , --家族成员父ID
    [Name] [nvarchar] (20) ,--家族成员名称
    [Sub_Family] [nvarchar] (4) , --是否续族,男性默认续族
    [Life] [int] ,--家族辈分排世
    [Sex] [nvarchar] (4))
go

-- 测试数据
insert into Family(id,pid,name,sub_family,life,sex)
select '0001','',    '周0001','是',1,'男' union all
select '0002','0001','周0012','是',2,'男' union all
select '0003','0001','周0013','是',2,'男' union all
select '0004','0001','周0014','否',2,'女' union all
select '0005','0003','周0031','是',3,'男' union all
select '0006','0003','周0032','否',3,'女' union all
select '0007','0003','周0033','是',3,'男' union all
select '0008','0002','周0021','是',3,'男' union all
select '0009','0002','周0022','否',3,'女' union all
select '0010','0002','周0023','是',3,'男' union all
select '0011','0004','周0041','是',3,'男' union all
select '0012','0007','周0071','是',4,'男' union all
select '0013','0007','周0072','否',4,'男' union all
select '0014','0005','周0051','是',4,'男' union all
select '0015','0013','周0131','是',5,'男'

-- 建立函数
if object_id('fm_Cid')is not null
drop function fm_Cid
go

-- fm_Cid('0001',0) 查ID='0001'的所有,Sub=1时,只查 Sub_Family='是'
-- select a.ID, a.PID, a.Name, a.Sub_Family, a.Life, a.Sex FROM Family a,dbo.fm_cid('0001',0) b where a.id=b.id
create FUNCTION fm_Cid(@ID varchar(10),@Sub int)
 RETURNS @t_Level TABLE(ID varchar(10),Level int,Sub_Family varchar(4))
 AS
 BEGIN
  DECLARE @Level int
  SET @Level=1
  if @Sub=0
     INSERT @t_Level SELECT @ID,@Level,Sub_Family FROM Family where Id=@id
  else
     INSERT @t_Level SELECT @ID,@Level,Sub_Family FROM Family where Id=@id and Sub_Family='是'

  WHILE @@ROWCOUNT>0
  BEGIN
   SET @Level=@Level+1
   INSERT @t_Level SELECT a.ID,@Level,a.Sub_Family
    FROM Family a,@t_Level b
    WHERE a.PID=b.ID AND b.Level=@Level-1
  END

  RETURN
 END
go

select a.ID, a.PID, a.Name, a.Sub_Family, a.Life, a.Sex FROM Family a,dbo.fm_cid('0001',0) b where a.id=b.id