if OBJECT_ID('tb')is not null
drop table tb
go
if OBJECT_ID('c_f')is not null
drop function c_f
go
create table tb(id int,pid int, puborpri int ,name nvarchar(10))
insert into tb select 1,0,0,'精品杂志'
union all select 2,1,0,'财富英雄'
union all select 3,1,0,'工作之余'
union all select 4,2,0,'比尔'
union all select 5,3,0,'跑步'
go
create function c_f
(@id int)
returns @tb table(id int ,level int)
as
begin
declare @level int
set @level=1
insert into @tb select @id,@levelwhile @@rowcount>0
begin
set @level=@level+1
insert into @tb select a.id,@level from tb a join @tb b on a.pid=b.id and b.level=@level-1
end
return
end
go
declare @name nvarchar(100)
set @name=''
select @name=@name+ '/'+b.name from dbo.c_f(1) a join tb b on a.id=b.id
select STUFF(@name,1,1,'')
----------------------------------------------------------------------------------------------------
精品杂志/财富英雄/工作之余/比尔/跑步(1 行受影响)
drop table tb
go
if OBJECT_ID('c_f')is not null
drop function c_f
go
create table tb(id int,pid int, puborpri int ,name nvarchar(10))
insert into tb select 1,0,0,'精品杂志'
union all select 2,1,0,'财富英雄'
union all select 3,1,0,'工作之余'
union all select 4,2,0,'比尔'
union all select 5,3,0,'跑步'
go
create function c_f
(@id int)
returns @tb table(id int ,level int)
as
begin
declare @level int
set @level=1
insert into @tb select @id,@levelwhile @@rowcount>0
begin
set @level=@level+1
insert into @tb select a.id,@level from tb a join @tb b on a.pid=b.id and b.level=@level-1
end
return
end
go
declare @name nvarchar(100)
set @name=''
select @name=@name+ '/'+b.name from dbo.c_f(1) a join tb b on a.id=b.id
select STUFF(@name,1,1,'')
----------------------------------------------------------------------------------------------------
精品杂志/财富英雄/工作之余/比尔/跑步(1 行受影响)
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-12 08:32:49
---------------------------------
--> 生成测试数据表:tbIf not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([id] int,[pid] int,[puborpri] int,[name] nvarchar(4))
Insert tb
Select 1,0,0,'精品杂志' union all
Select 2,1,0,'财富英雄' union all
Select 3,1,0,'工作之余' union all
Select 4,2,0,'比尔' union all
Select 5,3,0,'跑步'
Go
--Select * from tb-->SQL2005查询如下:
;with t as
(
select * from tb where id=1
union all
select tb.* from tb,t where tb.pid=t.id
)
select
stuff((select '/'+[name] from t for xml path('')),1,1,'') as [path]
/*
path
------------------------------------------------
精品杂志/财富英雄/工作之余/跑步/比尔(1 行受影响)
*/
create table tb(id varchar(10),pid varchar(10), puborpri int ,name nvarchar(10))
insert into tb select 1,0,0,'精品杂志'
union all select 2,1,0,'财富英雄'
union all select 3,1,0,'工作之余'
union all select 4,2,0,'比尔'
union all select 5,3,0,'跑步'
go
CREATE FUNCTION f_Cid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3),Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @ID,@Level
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level FROM tb a,@t_Level b WHERE a.PID=b.ID AND b.Level=@Level-1
END
RETURN
END
GO declare @s varchar(50)
select @s=isnull(@s+'/','')+a.name from tb a,f_Cid('2') b WHERE a.ID=b.ID
select @s
/*
--------------------------------------------------
财富英雄/比尔(所影响的行数为 1 行)*/declare @s varchar(50)
select @s=isnull(@s+'/','')+a.name from tb a,f_Cid('1') b WHERE a.ID=b.ID
select @s/*
--------------------------------------------------
精品杂志/财富英雄/工作之余/比尔/跑步(所影响的行数为 1 行)*/
drop table tb
drop function f_Cid
最好能做成一个函数。
已知函数名(id,下面文件的ID)就能查出来
精品杂志/工作之余/跑步/。。/。。 比尔 不是继承 跑步的啊
create table tb(id varchar(10),pid varchar(10), puborpri int ,name nvarchar(10))
insert into tb select 1,0,0,'精品杂志'
union all select 2,1,0,'财富英雄'
union all select 3,1,0,'工作之余'
union all select 4,2,0,'比尔'
union all select 5,3,0,'跑步'
go
CREATE FUNCTION f_Cid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @ID,@Level,@ID from tb where pid=0
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level,rtrim(b.Sort)+a.ID FROM tb a,@t_Level b WHERE a.PID=b.ID AND b.Level=@Level-1
END
RETURN
END
GO
declare @s varchar(50)
select @s=isnull(@s+'/','')+a.name from tb a,f_Cid('1') b WHERE a.ID=b.ID order by sort
select @s/*
--------------------------------------------------
精品杂志/财富英雄/比尔/工作之余/跑步(所影响的行数为 1 行)
*/
精品杂志/财富英雄/比尔 函数名(1, 4)
精品杂志/工作之余/跑步 函数名(1, 5)谢谢!
这个只要反过来操作,由下面文件的ID往上找直到ID
drop table tb
go
if OBJECT_ID('f_getparent')is not null
drop function f_getparent
go
create table tb(id int,pid int, puborpri int ,name nvarchar(10))
insert into tb select 1,0,0,'精品杂志'
union all select 2,1,0,'财富英雄'
union all select 3,1,0,'工作之余'
union all select 4,2,0,'比尔'
union all select 5,3,0,'跑步'
go
--创建用户定义函数,每个子节点de父节点的信息
create function f_getParent(@ID int,@endid int)
returns varchar(40)
as
begin
declare @ret varchar(40)
select @ret = isnull(@ret ,'') +'\'+ name from tb where @endid = id
while exists(select 1 from tb where ID=@endid and id >=@id and pID<>0 )
begin
select @endid=b.ID,@ret='\'+rtrim(b.name)+isnull(@ret,'')
from
tb a,tb b
where
a.ID=@endid and b.ID=a.pID AND A.ID >= @ID
end
set @ret=stuff(@ret,1,1,'')
return @ret
end
go--执行查询
select isnull(dbo.f_getparent(1,4),'') as parentID
parentID
----------------------------------------
精品杂志\财富英雄\比尔select isnull(dbo.f_getparent(1,5),'') as parentIDparentID
----------------------------------------
精品杂志\工作之余\跑步
go