这个 ，  有些思路。  
请等一下， 我写写试试。

create procedure sp_test
as
begin
   select
       a.node_id,
       a.parentNode_id,
       a.node_text,
       b.node_id   as ancestor_id  ,
       b.node_text as ancestor_text      
   into
       #t
   from
       xkb_treeNode a,xkb_treeNode b
   where
       a.parentNode_id = b.node_id and a.isModule = 1  
   
   while(exists(select 1 from xkb_treeNode a,#t b where a.node_id=ancestor_id and a.parentNode_id != -1))
   begin
       update #t
       set
           ancestor_id   = b.p_id,
           ancestor_text = b.p_text
       from
           #t a,
           (select
               c.node_id,
               d.node_id as p_id,
               d.node_text as p_text
            from
               xkb_treeNode c,xkb_treeNode d
            where
               c.parentNode_id = d.node_id) b
       where
           a.ancestor_id = b.node_id
   end
   
   select * from #t order by node_id
end

真是太感谢了 。    

呵呵 忙死我了