一问一答：存储过程经典问题
只涉及到一个表：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