Create Table test2(
id1 varchar(10),
level1 int,
pid varchar(10),
name1 varchar(8))
Insert into test2 values('uiyui', 2, 'jlkjkl', 'n002')
Insert into test2 values('jlkjkl', 1, '[null]', 'n001')
Insert into test2 values('bmnbjk', 3, 'uiyui', 'n003')
----------------------------------------
----首先創function----------
--------------Create Function-----------------
Create Function getname(@a int)
returns varchar(1000)
as
begin
Declare @s varchar(1000)
set @s=''
select @s=@s +name1+'-' from test2 where level1<=@a order by level1
set @s=substring(@s,1,len(@s)-1)
return(@s)
end
-----------------------------------------------再執行這條語句-------------
select *,name2=dbo.getname(level1) from test2
如果记录很多,同级的的项目会有重复,但是一个子级项,不可能对应它上一级所有的项,而你的
select @s=@s +name1+'-' from test2 where level1<=@a order by level1这条语句应该是把所有的上级项都加了进来?