declare @MemberID int; declare @topID int; select @topID=ParentAgencyID from Web_vUserList where userId=267898; set @MemberID=267898; WITH dc (UserID, UserName, NickName, ParentAgencyID,AgencyName) AS ( -- Anchor member definition,最顶级,我们用0级来表示 SELECT e.UserID, e.UserName, e.NickName, e.ParentAgencyID,e.AgencyName--,0 AS Level FROM Web_vUserList AS e where e.UserID =@MemberID UNION ALL -- Recursive member definition,其他各级,我们用1,2,3……级来表示 SELECT e.UserID, e.UserName, e.NickName, e.ParentAgencyID,e.AgencyName--,Level + 1 FROM dc AS d INNER JOIN Web_vUserList AS e ON e.UserID = d.ParentAgencyID where (e.UserID<>@topID ) ) -- Recursive member definition 递归查询的Sql,还可以嵌套使用update语句哟! SELECT * FROM dc AS dp 我这样写应该是解决问题了。但是好像效率稍微低了一点。
你的上线是不是指上层级的数据 你以上不就是取的上层级的数据吗? 取下级把 INNER JOIN Web_vUserList AS e ON e.UserID = d.ParentAgencyID 改为 : INNER JOIN Web_vUserList AS e ON d.UserID = e.ParentAgencyID
WITH dc (UserID, UserName, NickName, ParentAgencyID,AgencyName) AS ( SELECT e.UserID, e.UserName, e.NickName, e.ParentAgencyID,e.AgencyName--,0 AS Level FROM Web_vUserList AS e where e.UserID =267898 UNION ALL SELECT e.UserID, e.UserName, e.NickName, e.ParentAgencyID,e.AgencyName--,Level + 1 FROM dc AS d INNER JOIN Web_vUserList AS e ON e.ParentAgencyID = d.UserID ) SELECT * FROM dc AS dp
大约类似于这样试试WITH dc1 (UserID, UserName, NickName, ParentAgencyID,AgencyName) AS ( -- Anchor member definition,最顶级,我们用0级来表示 SELECT e.UserID, e.UserName, e.NickName, e.ParentAgencyID,e.AgencyName--,0 AS Level FROM Web_vUserList AS e where e.UserID =267898 UNION ALL -- Recursive member definition,其他各级,我们用1,2,3……级来表示 SELECT e.UserID, e.UserName, e.NickName, e.ParentAgencyID,e.AgencyName--,Level + 1 FROM dc AS d INNER JOIN Web_vUserList AS e ON e.UserID = d.ParentAgencyID --where (e.UserID=467083 ) ), dc2 (UserID, UserName, NickName, ParentAgencyID,AgencyName) AS ( -- Anchor member definition,最顶级,我们用0级来表示 SELECT e.UserID, e.UserName, e.NickName, e.ParentAgencyID,e.AgencyName--,0 AS Level FROM Web_vUserList AS e where e.UserID =267898 UNION ALL -- Recursive member definition,其他各级,我们用1,2,3……级来表示 SELECT e.UserID, e.UserName, e.NickName, e.ParentAgencyID,e.AgencyName--,Level + 1 FROM dc AS d INNER JOIN Web_vUserList AS e ON d.UserID = e.ParentAgencyID --where (e.UserID=467083 ) ) SELECT * FROM dc1 union all SELECT * FROM dc2 order by UserID, ParentAgencyID
declare @MemberID int; declare @topID int; select @topID=ParentAgencyID from Web_vUserList where userId=267898; set @MemberID=267898; WITH dc (UserID, UserName, NickName, ParentAgencyID,AgencyName) AS ( -- Anchor member definition,最顶级,我们用0级来表示 SELECT e.UserID, e.UserName, e.NickName, e.ParentAgencyID,e.AgencyName--,0 AS Level FROM Web_vUserList AS e where e.UserID =@MemberID UNION ALL -- Recursive member definition,其他各级,我们用1,2,3……级来表示 SELECT e.UserID, e.UserName, e.NickName, e.ParentAgencyID,e.AgencyName--,Level + 1 FROM dc AS d INNER JOIN Web_vUserList AS e ON e.UserID = d.ParentAgencyID where (e.UserID<>@topID ) ) -- Recursive member definition 递归查询的Sql,还可以嵌套使用update语句哟! SELECT * FROM dc AS dp 这个应该可行的
declare @topID int;
select @topID=ParentAgencyID from Web_vUserList where userId=267898;
set @MemberID=267898;
WITH dc (UserID, UserName, NickName, ParentAgencyID,AgencyName)
AS
(
-- Anchor member definition,最顶级,我们用0级来表示
SELECT e.UserID, e.UserName, e.NickName, e.ParentAgencyID,e.AgencyName--,0 AS Level
FROM Web_vUserList AS e
where e.UserID =@MemberID
UNION ALL
-- Recursive member definition,其他各级,我们用1,2,3……级来表示
SELECT e.UserID, e.UserName, e.NickName, e.ParentAgencyID,e.AgencyName--,Level + 1
FROM dc AS d
INNER JOIN Web_vUserList AS e ON e.UserID = d.ParentAgencyID
where (e.UserID<>@topID )
)
-- Recursive member definition 递归查询的Sql,还可以嵌套使用update语句哟!
SELECT * FROM dc AS dp 我这样写应该是解决问题了。但是好像效率稍微低了一点。
你以上不就是取的上层级的数据吗?
取下级把
INNER JOIN Web_vUserList AS e ON e.UserID = d.ParentAgencyID
改为 :
INNER JOIN Web_vUserList AS e ON d.UserID = e.ParentAgencyID
WITH dc (UserID, UserName, NickName, ParentAgencyID,AgencyName)
AS
(
SELECT e.UserID, e.UserName, e.NickName, e.ParentAgencyID,e.AgencyName--,0 AS Level
FROM Web_vUserList AS e
where e.UserID =267898
UNION ALL
SELECT e.UserID, e.UserName, e.NickName, e.ParentAgencyID,e.AgencyName--,Level + 1
FROM dc AS d
INNER JOIN Web_vUserList AS e ON e.ParentAgencyID = d.UserID
)
SELECT * FROM dc AS dp
AS
(
-- Anchor member definition,最顶级,我们用0级来表示
SELECT e.UserID, e.UserName, e.NickName, e.ParentAgencyID,e.AgencyName--,0 AS Level
FROM Web_vUserList AS e
where e.UserID =267898
UNION ALL
-- Recursive member definition,其他各级,我们用1,2,3……级来表示
SELECT e.UserID, e.UserName, e.NickName, e.ParentAgencyID,e.AgencyName--,Level + 1
FROM dc AS d
INNER JOIN Web_vUserList AS e ON e.UserID = d.ParentAgencyID
--where (e.UserID=467083 )
),
dc2 (UserID, UserName, NickName, ParentAgencyID,AgencyName)
AS
(
-- Anchor member definition,最顶级,我们用0级来表示
SELECT e.UserID, e.UserName, e.NickName, e.ParentAgencyID,e.AgencyName--,0 AS Level
FROM Web_vUserList AS e
where e.UserID =267898
UNION ALL
-- Recursive member definition,其他各级,我们用1,2,3……级来表示
SELECT e.UserID, e.UserName, e.NickName, e.ParentAgencyID,e.AgencyName--,Level + 1
FROM dc AS d
INNER JOIN Web_vUserList AS e ON d.UserID = e.ParentAgencyID
--where (e.UserID=467083 )
)
SELECT * FROM dc1
union all
SELECT * FROM dc2
order by UserID, ParentAgencyID
我的意思是这样的。
一个树形里面, 有个父节点是 1003,而他的子孙里面有一个节点是 1003050802,大概是这样的。现在将
1003 和 1003050802 传进来。要得到
1003 ->100305->10030508->1003050802
这个路径
而不是将它们的所有数都得到。
declare @MemberID int;
declare @topID int;
select @topID=ParentAgencyID from Web_vUserList where userId=267898;
set @MemberID=267898;
WITH dc (UserID, UserName, NickName, ParentAgencyID,AgencyName)
AS
(
-- Anchor member definition,最顶级,我们用0级来表示
SELECT e.UserID, e.UserName, e.NickName, e.ParentAgencyID,e.AgencyName--,0 AS Level
FROM Web_vUserList AS e
where e.UserID =@MemberID
UNION ALL
-- Recursive member definition,其他各级,我们用1,2,3……级来表示
SELECT e.UserID, e.UserName, e.NickName, e.ParentAgencyID,e.AgencyName--,Level + 1
FROM dc AS d
INNER JOIN Web_vUserList AS e ON e.UserID = d.ParentAgencyID
where (e.UserID<>@topID )
)
-- Recursive member definition 递归查询的Sql,还可以嵌套使用update语句哟!
SELECT * FROM dc AS dp
这个应该可行的