[AgentFlow]部門組織查詢(遞迴查詢)，由上而下或由下而上

--某部門從某部門往上找所有部門
DECLARE @DEPIDUP VARCHAR(20)
DECLARE @NUM INT
SET @DEPIDUP = 'DEP00061590139401173'
SET @NUM = 0
;
WITH DEPUP AS(
    SELECT DEPID , PARENTID , 0 LEVEL FROM Dep_GenInf WHERE 1=1  AND DepID = @DEPIDUP
    UNION ALL
    SELECT B.DEPID , B.PARENTID , @NUM +1 LEVEL  FROM  DEPUP A , Dep_GenInf B WHERE A.PARENTID = B.DepID
)
SELECT DUP.DepID , RG.RolID , RG.Name , MG.MemID , MG.UserName
  FROM DEPUP DUP
  LEFT JOIN Rol_GenInf RG ON DUP.DepID = RG.DepID
  LEFT JOIN Mem_GenInf MG ON RG.RolID = MG.MainRoleID


--某部門從某部門往下找所有部門
DECLARE @DEPIDDOWN VARCHAR(20)
SET @DEPIDDOWN = 'DEP00041590139241137'
;
WITH DEPDOWN AS(
    SELECT DEPID , PARENTID   FROM Dep_GenInf WHERE 1=1 AND DepID = @DEPIDDOWN --AND ParentID = 'company'
    UNION ALL
    SELECT B.DEPID , B.PARENTID FROM DEPDOWN A , Dep_GenInf B WHERE A.DepID = B.PARENTID
)
SELECT DUD.DepID , RG.RolID , RG.Name , MG.MemID , MG.UserName
  FROM DEPDOWN DUD
  LEFT JOIN Rol_GenInf RG ON DUD.DepID = RG.DepID
  LEFT JOIN Mem_GenInf MG ON RG.RolID = MG.MainRoleID