获取下级用户列表
ALTER procedure [dbo].[GetUserList](@iid int)
AS
BEGIN
DECLARE @tmp NVARCHAR(max);
SET @tmp='';
with RelClass
as
(
select *,0 as Level,cast('0' as nvarchar(max)) as treepath from Com_Operator WHERE OperatorID = @iid
union all
select csc.*,rc.[Level] + 1,rc.treepath + dbo.Lpad(Row_Number() over (order by csc.ListSort desc),8) as treepath
from Com_Operator as csc
inner join RelClass as rc on csc.parentID = rc.OperatorID )
SELECT @tmp =@tmp+CONVERT(VARCHAR(255),RelClass.UserID )+',' from RelClass order by treepath
select substring(@tmp,1,len(@tmp)-1)
END
AS
BEGIN
DECLARE @tmp NVARCHAR(max);
SET @tmp='';
with RelClass
as
(
select *,0 as Level,cast('0' as nvarchar(max)) as treepath from Com_Operator WHERE OperatorID = @iid
union all
select csc.*,rc.[Level] + 1,rc.treepath + dbo.Lpad(Row_Number() over (order by csc.ListSort desc),8) as treepath
from Com_Operator as csc
inner join RelClass as rc on csc.parentID = rc.OperatorID )
SELECT @tmp =@tmp+CONVERT(VARCHAR(255),RelClass.UserID )+',' from RelClass order by treepath
select substring(@tmp,1,len(@tmp)-1)
END