递归查询上一级

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test]') AND type in (N'U'))
DROP TABLE [dbo].[test]
GO

CREATE TABLE [dbo].[test](
[id] [int] NULL,
[name] [nvarchar](50) NULL,
[upid] [int] NULL
) ON [PRIMARY]

GO

insert into test select 1,'A',0
insert into test select 2,'B',1
insert into test select 3,'C',2
insert into test select 4,'D',1
insert into test select 5,'E',4
insert into test select 6,'F',3
insert into test select 7,'G',5
insert into test select 8,'H',6

--创建用户定义函数,每个子节点de父节点的信息
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[f_getParent]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[f_getParent]
GO

create function f_getParent(@ID int)
returns varchar(40)
as
begin
declare @ret varchar(40)
declare @str nvarchar(100)
while exists(select 1 from test where ID=@ID and upid<>0 and upid<>1)
begin
select @ID=b.ID--,@ret=','+rtrim(b.ID)+isnull(@ret,'')
,@ret=','+rtrim(b.name)+isnull(@ret,'')
from
test a,test b
where
a.ID=@ID and b.ID=a.upid
end

set @ret=stuff(@ret,1,1,'')
return @ret
end
go

--执行查询
select ID,isnull(dbo.f_getParent(ID),'') as parentID from test
go

 

查询结果：

ID parentID
1
2
3     B
4
5     D
6     B,C
7     D,E
8     B,C,F