with 递归

/*----------------------------------------------------------

  2009-7-2   本地 with　递归 

----------------------------------------------------------*/
use Test
go

--设置选中的成员
declare @oid int;
set @oid = 1;

--递归查询所有子成员
WITH locs(memberid,membername,parentmemberid)
AS
(
    SELECT memberid,membername,parentmemberid
 FROM Family
    WHERE memberid = @oid
    UNION ALL
    SELECT f.memberid,f.membername,f.parentmemberid
 FROM Family f INNER JOIN locs l ON l.memberid = f.parentmemberid
)
select * from locs
go