一问一答：存储过程经典问题

只涉及到一个表：xkb_treeNode

表结构是这样：
node_id          int      //节点id
parentNode_id    int      //父节点id
node_text        varchar  //节点内容
isModule         bit      //是否叶子节点

现在保存的数据有：

node_id  parentNode_id  node_text        isModule
   1        -1          语言与文学           0
   2        -1            数学               0
   3        -1            技术               0
   4         1            语文               0
   5         1            外语               0
   6         5            英语               0
   7         6          初中英语             0
   8         7           特斯塔              1
   9         4           测定是2             1
   10        2            测试3              1


现在问题是：
能否通过做一个存储过程，
根据表中的isModule字段的取值（取值为1的表示最终叶子结点），
比如“特斯塔”为叶子节点，层层向上递进找到”特斯塔“的祖先节点：
特斯塔-〉初中英语-〉英语-〉外语-〉语言与文学
即通过”特斯塔“找到”语言与文学“来

最终返回的形态为：
叶子节点id  父节点id      节点名称      祖先节点名称  祖先节点id
   8           7           特斯塔        语言与文学       1
   9           4           测定是2       语言与文学       1
   10          2           测试3           数学           2

 

/////////////////////////////////////////////////////////////////////////
正确答案：

 --生成测试数据
create table xkb_treeNode(
node_id        int,
parentNode_id   int,
node_textvarchar(10),
isModulebit)


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

原文转自：http://www.ltesting.net