mysql获取当前节点的所有叶子节点_mssql sqlserver 如何获取一个叶子节点下所有子节点呢？...

摘要：

下文sql技巧—获取所有子节点的方法分享，如下所示；

实现思路:

通过while循环依次遍历节点，然后将起添加到临时表中返回

即可通过节点获取所有子节点

例:

create table [maomao365.com]

(keyId int,parentId int,n nvarchar(10))

go

insert into [maomao365.com]

(keyId,parentId,n)values

(1,0,'总经办'),

(2,1,'销售部'),

(3,1,'财务部'),

(4,1,'客服部'),

(5,2,'销售1部'),

(6,2,'销售2部')

go

/*

创建获取所有子节点的sql函数

*/

CREATE FUNCTION [dbo].[fn_getTreeAll]

(

@keyId int

)

RETURNS @a table(keyId int,n nvarchar(10))

AS

begin

insert into @a(keyId,n)

select keyId,n from [maomao365.com]

where keyId =@keyId

while exists(select null from [maomao365.com]

where parentId in (select keyid from @a)

and keyId not in (select keyId from @a)

)

begin

insert into @a(keyId,n)

select keyId,n from [maomao365.com]

where parentId in (select keyid from @a)

and keyId not in (select keyId from @a)

end

return;

end;

go

select * from [dbo].[fn_getTreeAll](2)

go

drop FUNCTION [dbo].[fn_getTreeAll]

go

truncate table [maomao365.com]

drop table [maomao365.com]

go