declare @i int ,@row int;

set @i=0;

set @row=0;

declare @temp table(JX_TargetID uniqueidentifier,ParentID uniqueidentifier);

insert @temp select JX_TargetID,ParentID from JX_Target where JX_TargetID

='3237CA9D-3CFA-436C-A352-1711CD9A3914';

while @i<=@row

begin

insert @temp select JX_TargetID,ParentID from JX_Target a where exists (

select * from @temp b where a.ParentID= b.JX_TargetID

) and not exists (

select * from @temp b where a.JX_TargetID= b.JX_TargetID

);

set @i=@i+1;

set @row=(select count(1) from @temp);

end

select * from JX_Target where JX_TargetID in

 (select JX_TargetID from @temp);

使用 with 进行对表的递归查询，只能针对于 sql 2005 sql 2008 使用。而 sql 2000　的数据库无法使用这个sql语句。

以上，是兼容sql server　的sql语句。

• 上一篇：sql 2005 使用with
• 下一篇：sql server 表变量临时表的区别