多层次结构的问题

happydaily 2014-08-07 10:15:16
有一个多层次的表,目前只用到三层,有表结构和数据如下:
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,现在就是迷茫的是查询到某个结果如何告诉它还要向上找父级直到顶级,求指导,谢谢!
...全文
201 3 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
3 条回复
切换为时间正序
请发表友善的回复…
发表回复
reenjie 2014-08-08
  • 打赏
  • 举报
回复
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可以作為參數傳入
习惯性蹭分 2014-08-08
  • 打赏
  • 举报
回复

 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
save4me 2014-08-08
  • 打赏
  • 举报
回复

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

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧