34,838
社区成员




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
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
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