再发一个100分问题：如何找到这个层次结构的祖先？

Question

--生成测试数据
create table xkb_treeNode(
node_id         int,
parentNode_id   int,
node_text varchar(10),
isModule bit)
insert into xkb_treeNode select 1  ,-1,'语言与文学' ,0
insert into xkb_treeNode select 2  ,-1,'数学' ,0
insert into xkb_treeNode select 3  ,-1,'技术' ,0
insert into xkb_treeNode select 4  , 1,'语文' ,0
insert into xkb_treeNode select 5  , 1,'外语' ,0
insert into xkb_treeNode select 6  , 5,'英语' ,0
insert into xkb_treeNode select 7  , 6,'初中英语' ,0
insert into xkb_treeNode select 8  , 7,'特斯塔'         ,1
insert into xkb_treeNode select 9  , 4,'测定是2' ,1
insert into xkb_treeNode select 10 , 2,'测试3' ,1
--创建存储过程
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 = node_id and b.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
--执行存储过程，结果楼主自己看
exec sp_test

Answer 1

create table tree 
(
m_id int,p_id int ,name char(30))
insert into tree
select 1,2,'a'
union
select 2,-1,'b'
union
select 1,5,'c'
union
select 5,2,'d'
。。

Answer 2

create table tree 
(
m_id int,p_id int ,name char(30))
insert into tree
select 6,5,'a'
union
select 2,-1,'b'
union
select 1,5,'c'
union
select 5,2,'d'select * from tree
--delete from treecreate proc t
@ mname char(20)
as
select distinct m.m_id,m.p_id,m.name mname,p.name pname 
from 
(select * from tree) p,
(select * from tree )m
where p.p_id=m.m_id and
m.name='d'

Answer 3

create table xkb_treeNode(
node_id         int,
parentNode_id    int,
node_text         varchar(30),
isModule         bit )
insert into  xkb_treeNode
 select   1, -1,'語文學', 0  union all
 select      2, -1,'數學', 0  union all
 select      3, -1,'技注', 0  union all
 select      4, 1,'語文',  0  union all
 select      5, 1,'外語', 0  union all
 select      6,5,'英語',0  union all
 select      7, 6,'初中英語', 0  union all
 select      8,7, '特斯塔', 1   union all
 select      9, 4,'測定是2', 1  union all
 select      10, 2,'測試3', 1 
-------------------------------------------------------------------------------------下面是建過程進行處理---------------------
create  proc  getc
@s  varchar(20)
as
declare  @node  int,@i  int,@p  int,@d  int,@parent int,@chi  int
select  @i=count(*)   from xkb_treenode
select  @p=parentNode_id,@node=node_id  from xkb_treeNode  where Node_text=@s
set  @parent=@p
set  @chi=@node
set  @d=1
while @i>@d
begin
        if not  exists(select *  from xkb_treeNode where node_id=@p)
        begin
             select  叶子节点id=@chi,父节点id=@parent,节点名称=@s,祖先节点名称=node_text,
           祖先节点id=node_id  from xkb_treeNode  where  parentNode_id=@p and node_id=@node
              break
         end
         select  @p=parentNode_id,@node=node_id  from  xkb_treeNode  where node_id=@p
         set  @d=@d+1
end
------------------------------結束過程-------------------------------------------- exec  getc '特斯塔'         ---------執行過程  輸入 節點名稱---------
-------------輸出-----------
叶子节点id  父节点id      节点名称      祖先节点名称  祖先节点id
   8        7          特斯塔      语言与文学     1

Answer 4

以上 libin_ftsafe(子陌红尘) 的正解非常感谢~~~~，不过有个地方可能敲错了运行无误答案是 --生成测试数据
create table xkb_treeNode(
node_id         int,
parentNode_id   int,
node_text varchar(10),
isModule bit)
insert into xkb_treeNode select 1  ,-1,'语言与文学' ,0
insert into xkb_treeNode select 2  ,-1,'数学' ,0
insert into xkb_treeNode select 3  ,-1,'技术' ,0
insert into xkb_treeNode select 4  , 1,'语文' ,0
insert into xkb_treeNode select 5  , 1,'外语' ,0
insert into xkb_treeNode select 6  , 5,'英语' ,0
insert into xkb_treeNode select 7  , 6,'初中英语' ,0
insert into xkb_treeNode select 8  , 7,'特斯塔'         ,1
insert into xkb_treeNode select 9  , 4,'测定是2' ,1
insert into xkb_treeNode select 10 , 2,'测试3' ,1
--创建存储过程
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
--执行存储过程，结果楼主自己看
exec sp_test