查询经常用的SQL语句

在很多部门表，组织表中经常会设计到递归表:也就是说一个编号下面有子编号，子编号下面还有子编号......这样无穷无尽,这时候

create function getOGDetails(@ParentOG_ID int)
returns @t table (og_id int,ParentOG_ID int,level int)
as
begin
declare @i int
set @i=1
insert into @t
select og_id,ParentOG_ID,level=@i from d_organization where OG_ID=@ParentOG_ID 
while @@rowcount>0
begin
set @i=@i+1
insert into @t
select a.og_id,a.ParentOG_ID,@i from d_organization a left join @t b on a.ParentOG_ID=b.og_id
where b.level=@i-1
end
return
end



----使用方法如下：
select * from d_organization where og_id in ( select og_id from getOGDetails(5))

CREATE function [dbo].[getOGDetailsLowToUp](@og_Id int)
returns @t table (stand int,og_id int,organizationName varchar(50),ParentOG_ID int)
as
begin
        declare @i int
        declare @parentId int
        set @i=1
        insert into @t
        select @i,og_id,organizationName,ParentOG_ID from d_organization where og_ID=@og_Id
        set @parentId=(select ParentOG_ID from @t where stand=@i)
    while @parentId!=0
        begin
        set @i=@i+1
        insert into @t
        select @i,og_id,organizationName,ParentOG_ID from d_organization  where og_ID=@parentId
        set @parentId=(select ParentOG_ID from @t where stand=@i)
    end
    return
end


使用方法如下:
select * from d_organization where og_id in ( select og_id from [getOGDetailsLowToUp](45))

这样就可以查询出自己及自己父级的所有信息