多层次结构的问题

[happydaily]

有一个多层次的表，目前只用到三层，有表结构和数据如下：

CREATE TABLE [Project](
  [id] [int] NULL,
  [pid] [int] NULL,
  [name] [nvarchar](10)
 )
 GO
 INSERT INTO Project VALUES(1,0,'项目1')
 INSERT INTO Project VALUES(2,0,'项目2')
 INSERT INTO Project VALUES(3,1,'子项1')
 INSERT INTO Project VALUES(4,1,'子项2')
 INSERT INTO Project VALUES(5,2,'子项1')
 INSERT INTO Project VALUES(6,3,'孙项1')
 INSERT INTO Project VALUES(7,3,'孙项2')
 INSERT INTO Project VALUES(8,4,'孙项1')
 GO

 CREATE TABLE [UserArrange](
  [id] [int] NULL ,
  [ProjectID] [int] NULL,
  [UserID] [Int]
 )
 go

 Insert into UserArrange values(1,1,1001)
 Insert into UserArrange values(3,2,1002)
 Insert into UserArrange values(4,3,1003)
 Insert into UserArrange values(5,4,1001)
 Insert into UserArrange values(6,6,1004)
 Insert into UserArrange values(7,7,1002)
 go

现在要查询出某个UserID所安排的项目，只显示最顶级，比如10001，显示为项目1，1002显示项目2，1003显示为项目1，1004显示为项目1，现在就是迷茫的是查询到某个结果如何告诉它还要向上找父级直到顶级，求指导，谢谢！

[save4me]

WITH Arrange (id, ProjectID, UserID, pid, name)
AS (
SELECT T1.id
	, T1.ProjectID
	, T1.UserID
--	, T2.id
	, T2.pid
	, T2.name
FROM UserArrange T1
	INNER JOIN Project T2 ON T2.id = T1.ProjectID
UNION ALL
SELECT T1.id
	, T1.ProjectID
	, T1.UserID
--	, T2.id
	, T2.pid
	, T2.name
FROM Arrange T1
	INNER JOIN Project T2 ON T1.pid = T2.id
)
SELECT id
	, UserID
	, name
	, ProjectID
FROM Arrange
WHERE pid = 0
ORDER BY UserID
	, name
	, ProjectID

id	UserID	name	ProjectID
1	1001	项目1	1
5	1001	项目1	4
7	1002	项目1	7
3	1002	项目2	2
4	1003	项目1	3
6	1004	项目1	6

[习惯性蹭分]

 if object_id('fn_getUPlevel') is not null
 drop function fn_getupLevel
 
 go
 create function fn_getUPlevel(@projectID int)
 returns int
 as begin
  declare @id int
   ;with sel as(
   select id,pid,1 as [level] from project where id=@projectid
   union all
   select a.id,a.pid,[level]+1 as [level] from project a
   join sel b on a.id=b.pid
  )
  select top(1) @id=id from sel order by level desc
  return @id
 end

[reenjie]

with tbl as ( select * from Project a where a.id in (select ProjectID from UserArrange where UserID=1002) union all select b.* from Project b,tbl c where b.id=c.pid ) select distinct * from tbl where pid=0 其中userid可以作為參數傳入