34,590
社区成员
发帖
与我相关
我的任务
分享
ALTER FUNCTION [dbo].[getPath]
(
@id uniqueidentifier,
@tempId varchar(4000) =''
)
RETURNS varchar(4000)
AS
BEGIN
declare @str varchar(4000)
declare @pid varchar(50)
select @str = ISNULL(DeptName,'') ,@pid = ParentId, @tempId= ISNULL(@tempId,'')+ ','+ convert(varchar(50),@id) +',' From Department where ID = @id
if exists (select 1 from Department where Id= @pid)
BEGIN
--此判断非常重要,防止进入死循环,即自己为自己的父级
IF(CHARINDEX( ','+ convert(varchar(50),@pid) +',',@tempId) =0)
BEGIN
select @str = dbo.getPath(@pid,@tempId)+'/'+@str
END
END
ELSE
BEGIN
select @str = DeptName From Department where ID = @id
END
return ISNULL(@str,'')
END
DECLARE @t_TB TABLE ([id] tinyint,[uname] NVARCHAR(10),[pid] tinyint);
INSERT INTO @t_TB VALUES
('1','a',0 ),
('2','b',0 ),
('3','c',1 ),
('4','d',3 );
;with cte as(
select * from @t_TB where id=1
union all select a.* from @t_TB a join cte on a.pid=cte.id
) select * from cte
/*
id uname pid
1 a 0
3 c 1
4 d 3
*/
with mt as
(
select t.id , t.uname , t.pid from test t where id = 'x' -- 父节点
union all
select t.id , t.uname , t.pid from test t , mt where t.pid = mt.id
)
select * from mt
SELECT ID,
uname,
pid
FROM test WHERE uname = 'a' AND pid<>'0'