drop table tree
go
create table tree ( node int, child int, weight int)
go
insert into tree values (1,2,5)
insert into tree values (1,3,5)
insert into tree values (2,4,6)
insert into tree values (2,5,6)
insert into tree values (2,6,6)
insert into tree values (3,7,6)
insert into tree values (3,8,6)
insert into tree values (4,0,6)
insert into tree values (5,9,6)
insert into tree values (5,10,6)
insert into tree values (6,0,4)
insert into tree values (7,11,1)
insert into tree values (8,0,4)
insert into tree values (9,0,1)
insert into tree values (10,0,5)
insert into tree values (11,0,5)
--child为0代表是叶子节点
go
drop proc proc_tree
go
create proc proc_tree (@node int)
asbegin
set nocount on
create table #tmp1 ( node int, parent int)
create table #tmp2 ( node int, parent int)
insert #tmp1 select node,child from tree where node = @node
while exists(select * from #tmp1 where parent <> 0)
begin
insert #tmp2 select a.node,a.child from tree a,#tmp1 b where a.node = b.parent
delete from #tmp1 where parent <> 0
insert #tmp1 select * from #tmp2
truncate table #tmp2
end
select * from #tmp1
set nocount off
end
goproc_tree 1
go
create table tree ( node int, child int, weight int)
go
insert into tree values (1,2,5)
insert into tree values (1,3,5)
insert into tree values (2,4,6)
insert into tree values (2,5,6)
insert into tree values (2,6,6)
insert into tree values (3,7,6)
insert into tree values (3,8,6)
insert into tree values (4,0,6)
insert into tree values (5,9,6)
insert into tree values (5,10,6)
insert into tree values (6,0,4)
insert into tree values (7,11,1)
insert into tree values (8,0,4)
insert into tree values (9,0,1)
insert into tree values (10,0,5)
insert into tree values (11,0,5)
--child为0代表是叶子节点
go
drop proc proc_tree
go
create proc proc_tree (@node int)
asbegin
set nocount on
create table #tmp1 ( node int, parent int)
create table #tmp2 ( node int, parent int)
insert #tmp1 select node,child from tree where node = @node
while exists(select * from #tmp1 where parent <> 0)
begin
insert #tmp2 select a.node,a.child from tree a,#tmp1 b where a.node = b.parent
delete from #tmp1 where parent <> 0
insert #tmp1 select * from #tmp2
truncate table #tmp2
end
select * from #tmp1
set nocount off
end
goproc_tree 1
解决方案 »
- SQL2000 企业管理器中的表,存储过程,用户自定义函数能否按照最后修改日期排序
- 如何取得学历管理表中的最高学历
- 问一个很弱的问题
- 帮说下 不懂!
- 如何根据某个字段值生成多行记录的新表,通过语句实现,求高手解决。
- Sql多表查询
- mysql 插入中文进去后,去出来时什么都看不到
- 由BINARY VARCHAR 向 datetime 类型进行转换时出现的问题
- sql server字符串类型转换为整形
- 去除某字段中的换行等特殊字符
- Sql Server 中在后台用Getdate()生成的日期格式,如何去掉最后的时间毫秒的值,或置0??
- 触发器中,不能在事务内部执行过程 'sp_fulltext_catalog'?
asbegin
set nocount on
declare table tmp1 ( ParentID varchar(80), PartNo varchar(80),LinkQty int,HasChild bool)
declare table tmp2 ( ParentID varchar(80), PartNo varchar(80),LinkQty int,HasChild bool)
declare table tmp3 ( ParentID varchar(80), PartNo varchar(80),LinkQty int,HasChild bool)
insert tmp1 select * from yourtable where ParentID = @ParentID
insert tmp3 select * from yourtable where ParentID = @ParentID while exists(select * from tmp1 where PartNo <> NULL)
begin
insert tmp2 select a.* from yourtable a,tmp1 b where a.ParentID = b.PartNo
delete from tmp1 where PartNo <> NULL
insert tmp1 select * from tmp2
insert tmp3 select * from tmp2
delete from tmp2
end
select * from tmp1 union select * from tmp3
set nocount off
end
go