--测试: create table t1 (id int,name char(1),pid int,level int) insert t1 select 1, 'a' ,0, 1 union all select 2 , 'b' , 8 , 1 union all select 3 , 'c' , 0 , 3 union all select 4 , 'd' , 1 , 1 union all select 5 , 'e' , 1 , 2 union all select 6 , 'f' , 4 , 1 union all select 7 , 'g' , 4 , 2 union all select 8 , 'h' , 6 , 1 union all select 9 ,'i', 8 ,2--运行: declare @tb table(id int,name char(1),pid int,level int) insert @tb select id,name,pid,level from t1 where pid = 0 while @@rowcount > 0 begin insert @tb select id,name,pid,level from t1 where pid in (select id from @tb) and id not in (select id from @tb) end select * from @tb order by level,pid,id--结果: id name pid level ----------- ---- ----------- ----------- 1 a 0 1 4 d 1 1 6 f 4 1 8 h 6 1 2 b 8 1 5 e 1 2 7 g 4 2 9 i 8 2 3 c 0 3(所影响的行数为 9 行)
好象直接查询就行了。 --测试: create table t1 (id int,name char(1),pid int,level int) insert t1 select 1, 'a' ,0, 1 union all select 2 , 'b' , 8 , 1 union all select 3 , 'c' , 0 , 3 union all select 4 , 'd' , 1 , 1 union all select 5 , 'e' , 1 , 2 union all select 6 , 'f' , 4 , 1 union all select 7 , 'g' , 4 , 2 union all select 8 , 'h' , 6 , 1 union all select 9 ,'i', 8 ,2--运行:select * from t1 order by level,pid,id--结果: id name pid level ----------- ---- ----------- ----------- 1 a 0 1 4 d 1 1 6 f 4 1 8 h 6 1 2 b 8 1 5 e 1 2 7 g 4 2 9 i 8 2 3 c 0 3(所影响的行数为 9 行)
没有这么简单,请别考虑level这段,先假如没有这个字段,我要的只是和树的结构一样。
--这查查询处理: declare @tb table(id int,idm varchar(8000),level int) declare @level int set @level=0 insert @tb select id,cast(id as varchar),@level from t1 where pid = 0 while @@rowcount > 0 begin set @level=@level+1 insert @tb select a.id,b.idm+','+cast(a.id as varchar),@level from t1 a join @tb b on a.pid=b.id where b.level=@level-1 endselect replace(space(b.level),' ','-')+a.name from t1 a join @tb b on a.id=b.id order by b.idm
--下面是数据测试--测试数据 create table t1 (id int,name char(1),pid int,level int) insert t1 select 1,'a',0,1 union all select 2,'b',8,1 union all select 3,'c',0,3 union all select 4,'d',1,1 union all select 5,'e',1,2 union all select 6,'f',4,1 union all select 7,'g',4,2 union all select 8,'h',6,1 union all select 9,'i',8,2--查询处理 declare @tb table(id int,idm varchar(8000),level int) declare @level int set @level=0 insert @tb select id,cast(id as varchar),@level from t1 where pid = 0 while @@rowcount > 0 begin set @level=@level+1 insert @tb select a.id,b.idm+','+cast(a.id as varchar),@level from t1 a join @tb b on a.pid=b.id where b.level=@level-1 endselect replace(space(b.level),' ','-')+a.name from t1 a join @tb b on a.id=b.id order by b.idmgo --删除测试数据 drop table t1/*--测试结果 a -d --f ---h ----b ----i --g -e c --*/
--这查查询处理: declare @tb table(id int,idm varchar(8000),level int) declare @level int set @level=0 insert @tb select id,cast(id as varchar),@level from t1 where pid = 0 while @@rowcount > 0 begin set @level=@level+1 insert @tb select a.id,b.idm+','+cast(a.id as varchar),@level from t1 a join @tb b on a.pid=b.id where b.level=@level-1 endselect replace(space(b.level),' ','--')+a.name --完全按楼主的要求,这里应该是-- from t1 a join @tb b on a.id=b.id order by b.idm
--下面这种处理方法可以保证,在级别相同时,排序是按id的.--查询处理 declare @tb table(id int,idm varchar(8000),level int) declare @level int,@head varchar(20),@len int select @level=0,@len=len(aa),@head=space(@len) from(select aa=max(id) from t1) ainsert @tb select id,right(@head+cast(id as varchar),@len),@level from t1 where pid=0 while @@rowcount > 0 begin set @level=@level+1 insert @tb select a.id,b.idm+','+right(@head+cast(a.id as varchar),@len),@level from t1 a join @tb b on a.pid=b.id where b.level=@level-1 end--得到查询结果 select replace(space(b.level),' ','--')+a.name from t1 a join @tb b on a.id=b.id order by b.idm
http://www.csdn.net/Develop/Read_Article.asp?Id=18666http://www.csdn.net/Develop/list_article.asp?author=playyuer
create table t1 (id int,name char(1),pid int,level int)
insert t1 select 1, 'a' ,0, 1
union all select 2 , 'b' , 8 , 1
union all select 3 , 'c' , 0 , 3
union all select 4 , 'd' , 1 , 1
union all select 5 , 'e' , 1 , 2
union all select 6 , 'f' , 4 , 1
union all select 7 , 'g' , 4 , 2
union all select 8 , 'h' , 6 , 1
union all select 9 ,'i', 8 ,2--运行:
declare @tb table(id int,name char(1),pid int,level int)
insert @tb select id,name,pid,level from t1 where pid = 0
while @@rowcount > 0
begin
insert @tb select id,name,pid,level from t1 where pid in (select id from @tb)
and id not in (select id from @tb)
end
select * from @tb order by level,pid,id--结果:
id name pid level
----------- ---- ----------- -----------
1 a 0 1
4 d 1 1
6 f 4 1
8 h 6 1
2 b 8 1
5 e 1 2
7 g 4 2
9 i 8 2
3 c 0 3(所影响的行数为 9 行)
这样查询出来我就只要从上到下取记录显示就可以了,程序代码已经完成了,就是显示不出真实树的结构!!
--测试:
create table t1 (id int,name char(1),pid int,level int)
insert t1 select 1, 'a' ,0, 1
union all select 2 , 'b' , 8 , 1
union all select 3 , 'c' , 0 , 3
union all select 4 , 'd' , 1 , 1
union all select 5 , 'e' , 1 , 2
union all select 6 , 'f' , 4 , 1
union all select 7 , 'g' , 4 , 2
union all select 8 , 'h' , 6 , 1
union all select 9 ,'i', 8 ,2--运行:select * from t1 order by level,pid,id--结果:
id name pid level
----------- ---- ----------- -----------
1 a 0 1
4 d 1 1
6 f 4 1
8 h 6 1
2 b 8 1
5 e 1 2
7 g 4 2
9 i 8 2
3 c 0 3(所影响的行数为 9 行)
declare @tb table(id int,idm varchar(8000),level int)
declare @level int
set @level=0
insert @tb select id,cast(id as varchar),@level from t1 where pid = 0
while @@rowcount > 0
begin
set @level=@level+1
insert @tb select a.id,b.idm+','+cast(a.id as varchar),@level from t1 a join @tb b on a.pid=b.id where b.level=@level-1
endselect replace(space(b.level),' ','-')+a.name from t1 a join @tb b on a.id=b.id
order by b.idm
create table t1 (id int,name char(1),pid int,level int)
insert t1 select 1,'a',0,1
union all select 2,'b',8,1
union all select 3,'c',0,3
union all select 4,'d',1,1
union all select 5,'e',1,2
union all select 6,'f',4,1
union all select 7,'g',4,2
union all select 8,'h',6,1
union all select 9,'i',8,2--查询处理
declare @tb table(id int,idm varchar(8000),level int)
declare @level int
set @level=0
insert @tb select id,cast(id as varchar),@level from t1 where pid = 0
while @@rowcount > 0
begin
set @level=@level+1
insert @tb select a.id,b.idm+','+cast(a.id as varchar),@level from t1 a join @tb b on a.pid=b.id where b.level=@level-1
endselect replace(space(b.level),' ','-')+a.name from t1 a join @tb b on a.id=b.id
order by b.idmgo
--删除测试数据
drop table t1/*--测试结果
a
-d
--f
---h
----b
----i
--g
-e
c
--*/
declare @tb table(id int,idm varchar(8000),level int)
declare @level int
set @level=0
insert @tb select id,cast(id as varchar),@level from t1 where pid = 0
while @@rowcount > 0
begin
set @level=@level+1
insert @tb select a.id,b.idm+','+cast(a.id as varchar),@level from t1 a join @tb b on a.pid=b.id where b.level=@level-1
endselect replace(space(b.level),' ','--')+a.name --完全按楼主的要求,这里应该是--
from t1 a join @tb b on a.id=b.id
order by b.idm
declare @tb table(id int,idm varchar(8000),level int)
declare @level int,@head varchar(20),@len int
select @level=0,@len=len(aa),@head=space(@len)
from(select aa=max(id) from t1) ainsert @tb select id,right(@head+cast(id as varchar),@len),@level from t1 where pid=0
while @@rowcount > 0
begin
set @level=@level+1
insert @tb select a.id,b.idm+','+right(@head+cast(a.id as varchar),@len),@level
from t1 a join @tb b on a.pid=b.id where b.level=@level-1
end--得到查询结果
select replace(space(b.level),' ','--')+a.name from t1 a join @tb b on a.id=b.id
order by b.idm
http://expert.csdn.net/Expert/topic/2285/2285830.xml?temp=.454632