mssql2000递归

转:www.cnblogs.com/suxvsheng/archive/2008/07/30/1256353.html

create function dbo.f_get_data_by_recursion(@group_father_id int)
    returns @groups_result table (id int,name varchar(30),pid int,last_count int)
begin
    declare @count as int
    set @count=1

    insert @groups_result select id,name,pid,@count from admin_menu where id=@group_father_id
    while @@rowcount<>0


        begin
            set @count=@count+1
            insert @groups_result
            select g.id,g.name,g.pid,@count from admin_menu g,@groups_result gr
                where g.pid=gr.id and gr.last_count=@count-1
        end
    return    
end

GO


select top 10 * from admin_menu order by id desc
select * from f_get_data_by_recursion(119)

 表结构
id   父id 名字
id   pid   name