存储过程经典问题

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

2007-11-06 17:32

只涉及到一个表：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