sqlserver 查询下级和下级的所有子集

params += `with cte_child(di_departmentid,di_pid,level) 
			as
			(  
				--起始条件  
				select di_departmentid,di_pid,0 as level 
				from DEPARTMENT_INFO  
				where (di_pid = ` + strconv.Itoa(departmentid) + ` ) --列出父节点查询条件  
				union all 
				--递归条件  
				select a.di_departmentid,a.di_pid,b.level+1  
				from DEPARTMENT_INFO a  
				inner join  
				cte_child b  
				on ( a.di_pid=b.di_departmentid)   
			)`





sqlWhere +=" AND (ui_department_id  in (select  di_departmentid   from cte_child)  or ui_department_id=" + strconv.Itoa(departmentid) + ")  "

查询所有的父节点

--根据指定节点ID获取所有父节点--
WITH TEMP AS
(
SELECT * FROM table_name WHERE Id=50 --表的主键ID
UNION ALL
SELECT T0.* FROM TEMP,table_name T0 WHERE TEMP.ParentId=T0.Id --父级ID==子级ID
)
SELECT * FROM TEMP;

查询所有子节点，并以字符串形式展示 

WITH    cte_child ( fai_id, fai_parentid, level )
          AS (  
					--起始条件  
               SELECT   fai_id ,
                        fai_parentid ,
                        0 AS level
               FROM     FACTORY_AGENT_INFO
               WHERE    ( fai_parentid = 10 ) --列出父节点查询条件  
               UNION ALL 
					--递归条件  
               SELECT   a.fai_id ,
                        a.fai_parentid ,
                        b.level + 1
               FROM     FACTORY_AGENT_INFO a
                        INNER JOIN cte_child b ON ( a.fai_parentid = b.fai_id )
             )
    SELECT  CAST(fai_id AS VARCHAR(10)) + ','
    FROM    cte_child
FOR     XML PATH('')