ms-sql递归调用
今天早上碰到个问题,需要在MS_SQL中实现递归,两周前在整理数据的时候刚好实现了一个递归调用的方法,借此机会整理一下发上来,万一以需要可以自己拿出来用,不用再搜索 :)
当然为了方便搜索到的同学能够方便使用,也补充了使用的例子
----递归函数--------------------------------------------------------------------------
create function dbo.f_get_data_by_recursion
(
@group_father_id int
)
returns @groups_result table (group_id int,group_name varchar(50),group_father_id int,last_count int)
begin
declare @count as int
set @count=1
insert @groups_result select *,@count from groups where group_id=@group_father_id
while @@rowcount<>0
begin
set @count=@count+1
insert @groups_result
select g.group_id,g.group_name,g.group_father_id,@count from groups g,@groups_result gr
where g.group_father_id=gr.group_id and gr.last_count=@count-1
end
return
end
GO
----调用举例--------------------------------------------------------------------------
create table dbo.groups
(
group_id int,
group_name varchar(50),
group_father_id int
)
declare @loop as int
set @loop =1
while @loop < 1000
begin
insert into groups (group_id,group_name,group_father_id) values (@loop,'name',@loop -1)
set @loop =@loop +1
end
set @loop =2001
while @loop < 2500
begin
insert into groups (group_id,group_name,group_father_id) values (@loop,'name',@loop -1)
set @loop =@loop +1
end
select * from groups
select * from f_get_data_by_recursion(2001)
drop table dbo.groups
drop function dbo.f_get_data_by_recursion
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步