| 网站首页 | 业界新闻 | 群组 | 人才 | 下载频道 | 博客 | 代码贴 | 编程论坛
共有 449 人关注过本帖
标题:每日一学:SQL2000利用函数递归查询
只看楼主 收藏
mywisdom88
Rank: 20Rank: 20Rank: 20Rank: 20Rank: 20
等 级:版主
威 望:71
帖 子:2336
专家分:5572
注 册:2015-3-25
结帖率:98.86%
  已结贴   问题点数:20  回复次数:1   
每日一学: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

2017-10-11 16:41
厨师王德榜
Rank: 16Rank: 16Rank: 16Rank: 16
等 级:版主
威 望:63
帖 子:411
专家分:1415
注 册:2013-2-16
  得分:20 
好文章,便于新手理解“递归”的概念。
2017-10-16 11:18







关于我们 | 广告合作 | 编程中国 | 清除Cookies | TOP | 手机版

编程中国 版权所有,并保留所有权利。
Powered by Discuz, Processed in 0.058094 second(s), 8 queries.
Copyright©2004-2018, BCCN.NET, All Rights Reserved