--示例--示例数据
create table tb(id int,name varchar(10),parent int,depth int)
insert tb select 1,'aa',0,1
union all select 2,'bb',1,2
union all select 3,'cc',2,3
union all select 4,'dd',3,4
union all select 5,'ee',0,1
union all select 6,'ff',5,2
union all select 7,'gg',6,3
union all select 8,'hh',7,4
go--处理函数
create function f_URL(@id int)
returns varchar(8000)
as
begin
declare @r varchar(8000)
select @r=' > <a href=aa.aspx?id='+rtrim(id)
+'>'+rtrim(name)+'</a>',
@id=parent
from tb
where id=@id
while @@rowcount>0 and @id<>0
select @r=' > <a href=aa.aspx?id='+rtrim(id)
+'>'+rtrim(name)+'</a>'+@r,
@id=parent
from tb
where id=@id
return(stuff(@r,1,3,''))
end
go--调用函数实现查询
select dbo.f_URL(2)
select dbo.f_URL(4)
select dbo.f_URL(7)
go--删除测试
drop table tb
drop function f_URL/*--结果-----------------------------------------------------------
<a href=aa.aspx?id=1>aa</a> > <a href=aa.aspx?id=2>bb</a>(所影响的行数为 1 行)
-----------------------------------------------------------------------------------------------------------------------
<a href=aa.aspx?id=1>aa</a> > <a href=aa.aspx?id=2>bb</a> > <a href=aa.aspx?id=3>cc</a> > <a href=aa.aspx?id=4>dd</a>(所影响的行数为 1 行)
------------------------------------------------------------------------------------------
<a href=aa.aspx?id=5>ee</a> > <a href=aa.aspx?id=6>ff</a> > <a href=aa.aspx?id=7>gg</a>(所影响的行数为 1 行)
--*/
create table tb(id int,name varchar(10),parent int,depth int)
insert tb select 1,'aa',0,1
union all select 2,'bb',1,2
union all select 3,'cc',2,3
union all select 4,'dd',3,4
union all select 5,'ee',0,1
union all select 6,'ff',5,2
union all select 7,'gg',6,3
union all select 8,'hh',7,4
go--处理函数
create function f_URL(@id int)
returns varchar(8000)
as
begin
declare @r varchar(8000)
select @r=' > <a href=aa.aspx?id='+rtrim(id)
+'>'+rtrim(name)+'</a>',
@id=parent
from tb
where id=@id
while @@rowcount>0 and @id<>0
select @r=' > <a href=aa.aspx?id='+rtrim(id)
+'>'+rtrim(name)+'</a>'+@r,
@id=parent
from tb
where id=@id
return(stuff(@r,1,3,''))
end
go--调用函数实现查询
select dbo.f_URL(2)
select dbo.f_URL(4)
select dbo.f_URL(7)
go--删除测试
drop table tb
drop function f_URL/*--结果-----------------------------------------------------------
<a href=aa.aspx?id=1>aa</a> > <a href=aa.aspx?id=2>bb</a>(所影响的行数为 1 行)
-----------------------------------------------------------------------------------------------------------------------
<a href=aa.aspx?id=1>aa</a> > <a href=aa.aspx?id=2>bb</a> > <a href=aa.aspx?id=3>cc</a> > <a href=aa.aspx?id=4>dd</a>(所影响的行数为 1 行)
------------------------------------------------------------------------------------------
<a href=aa.aspx?id=5>ee</a> > <a href=aa.aspx?id=6>ff</a> > <a href=aa.aspx?id=7>gg</a>(所影响的行数为 1 行)
--*/
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货