先放代码：
CREATE function dbo.f_IdForAdColumn()
returns @re table(id int,parentid int,level int,sid varchar(8000),pre1 varchar(2),pre2 varchar(2)) --返回一张名为re的表
as
begin
declare @l int --声明一个变量
set @l=0 --对变量赋值
insert @re select id,parentid,@l,right(10000+id,4),'','' from tbAdColumn where ParentID=0
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.id,a.parentid,@l,b.sid+','+right(10000+a.id,4),'',''
from tbAdColumn a,@re b
where a.ParentID=b.id and b.level=@l-1
end
update a
set pre2= case when a.level>1 then
case id when (select convert(int,right(max(sid),4)) from @re where parentid=a.parentid and level=a.level) then 'A' else 'B' end
else
case id when (select convert(int,right(max(sid),4)) from @re where level=a.level) then 'A' else 'B' end
end
from @re a
where a.level>0
update a
set pre1=case when
left(sid,4)=(select convert(int,left(max(sid),4)) from @re where level=0) or
left(sid,4)=(select convert(int,right(max(sid),4)) from @re where level=1)
then ' '
else 'C' end
from @re a
where level>1
return
end
由于文章不能出现特殊字符，所以再给一张截图，各位大大对应着看看
对应视图：
SELECT TOP 100 PERCENT a.*,
CASE WHEN b.[LEVEL] = 0 THEN a.Title WHEN b.[LEVEL] = 1 THEN pre2 + replicate('-',
4) + a.Title ELSE b.pre1 + replicate(' ', (b.[LEVEL] - 1) * 4) + b.pre2 + replicate('-', 4)
+ a.Title END AS ReName, b.sid
FROM dbo.tbAdColumn a INNER JOIN
dbo.f_IdForAdColumn() b ON a.ID = b.id
ORDER BY b.sid
查出来的结果
谁能告诉我用户定义函数每一句是干嘛用的？还有函数中有一个'level'字段，我没有看他在表里面有啊！急急急
------解决方案--------------------
其实很简单的，想想应该能明白