sql -递归查询组织机构某节点下所有及最底层

1. 表

1.查询某一节点下的所有节点

WITH OrganizeTree AS ( SELECT name, pid, wid FROM Organize WHERE pid = '2362'
UNION ALL 
SELECT Organize.name, Organize.pid, Organize.wid FROM OrganizeTree 
JOIN Organize ON OrganizeTree.wid = Organize.pid)

SELECT name,wid,pid FROM OrganizeTree

2.查询某一节点下的最底层 节点

WITH OrganizeTree AS ( SELECT name, pid, wid from Organize where pid = '2362'
UNION ALL 
SELECT Organize.name, Organize.pid, Organize.wid from OrganizeTree 
JOIN Organize on OrganizeTree.wid = Organize.pid)
,leaf as ( 
select l.name,l.pid,l.wid from OrganizeTree l 
WHERE NOT EXISTS(SELECT c.name, c.pid, c.wid FROM OrganizeTree c WHERE l.wid = c.pid))
SELECT * FROM leaf