create table A (id int, parentID int, name varchar(500))
insert into A select 1 , 0 ,'A'
union all select 2 , 0, ' B'
union all select 3, 1 , 'AA'
union all select 4 , 3 , 'AAA'
union all select 5 , 2 , 'BB'
select *from Adeclare @temp varchar(800)
set @temp='-------------------------------------------------------------------------'
update a set name=substring(@temp,1,parentID)+name
對字段排序自己解決拉,呵呵。
insert into A select 1 , 0 ,'A'
union all select 2 , 0, ' B'
union all select 3, 1 , 'AA'
union all select 4 , 3 , 'AAA'
union all select 5 , 2 , 'BB'
select *from Adeclare @temp varchar(800)
set @temp='-------------------------------------------------------------------------'
update a set name=substring(@temp,1,parentID)+name
對字段排序自己解決拉,呵呵。
insert into A select 1 , 0 ,'A'
union all select 2 , 0, 'B'
union all select 3, 1 , 'AA'
union all select 4 , 3 , 'AAA'
union all select 5 , 2 , 'BB'
select *from Adeclare @temp varchar(800)
set @temp='-------------------------------------------------------------------------'
'B'多了個空格
排序:select * from A order by substring(name,parentID+1,100)update a set name=substring(@temp,1,parentID)+name
可以方便看出这个表的层次结构.方便用户添加数据时选择它的上级.
应该用临时表来做.
create function f_ccid(
)returns @re table([id] int,[level] int)
as
begin
declare @l int
set @l=0
insert @re select 0,@l
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.[id],@l
from [test] a,@re b
where a.[parentID]=b.[id] and b.[level]=@l-1
end
return
end
go
------------调用f_ccid()
select a.id,a.parentID,a.name,b.level,replicate('-',b.level)+a.name as name
from test a join f_ccid() b on a.id=b.id
------------返回结果id parentid name level name
1 0 A 1 -A
2 0 B 1 -B
3 1 AA 2 --AA
4 3 AAA 3 ---AAA
5 2 BB 2 --BB
case
when a.parentID=0 then a.name else replicate('-',b.level)+a.name
end as name
from test a join f_ccid() b on a.id=b.id
order by name
----------------------------------------------------
id parentid name level name
1 0 A 1 A
3 1 AA 2 --AA
4 3 AAA 3 ---AAA
2 0 B 1 B
5 2 BB 2 --BB
而随便写的.如果name的字段是中文,而且没有规律,那order by name就不能产生
上面的结果了吧.不管怎样,还是非常感谢你.
帮你up
:P
)returns @re table([id] int,[level] int,[parentName] varchar(800))
as
begin
declare @l int
set @l=0
insert @re select 0,@l,''
while @@rowcount>0
begin
set @l=@l+1
if (@l = 1)
insert @re select a.[id],@l,a.[name]
from [test] a,@re b
where a.[parentID]=b.[id] and b.[level]=@l-1
else
insert @re select a.[id],@l,c.[parentName]
from [test] a,@re b,@re c
where a.[parentID]=b.[id] and b.[level]=@l-1 and c.[id] = a.[parentID] and c.[parentName] is not null
end
return
end
go
-------------------------------------------------------
调用:
SELECT a.id, a.parentID, a.name, b. LEVEL, replicate('-', b. LEVEL) + a.name AS name,
replicate('-', b. LEVEL) + b.parentName AS parentname
FROM tree a JOIN
f_ccid() b ON a.id = b.id
ORDER BY replicate('-', b. LEVEL) + b.parentName
-----------------------------------------------------------
结果集
1 0 A 1 -A -A
2 1 AA 2 --AA --A
5 2 AAA 3 ---AAA ---A
3 0 B 1 -B -B
4 3 BB 2 --BB --B
SELECT a.id, a.parentID, a.name, b. LEVEL, replicate('-', b. LEVEL) + a.name AS name,
b.parentName
FROM test a JOIN f_ccid() b ON a.id = b.id
ORDER BY b.parentName,b.level
---------------------------------------------------------------
Result Set:
1 0 A 1 -A A
2 1 AA 2 --AA A
5 2 AAA 3 ---AAA A
3 0 B 1 -B B
4 3 BB 2 --BB B