MSSQL 下递归CTE的应用通过父级获取所有对应的子级节点（一）

引言：

比如说有如下人力资源管理模块下涉及到以下几个表

需要需要通过SQL构造出单位类别-单位（上级单位）-部门（上级部门）结构的树，如下图：

目前在数据表结果编码字段确定的条件下，我所能掌握的就是应用CTE递归（如果在有限层级关系的情况下可以采用层级编码，比如第一层1.X，对应的儿子，1.X.Y等等），WITH AS短语，也叫做子查询部分（subquery factoring），可以让你做很多事情，定义一个SQL片断，该SQL片断会被整个SQL语句所用到，可以简单理解为把一段ＳＱＬ片段对应的结果集取一个别名，其中’{@Search[‘Type’]}’只是我项目中的一个变量，他的值可以是单位类别Id，也可以是单位Id，也可以是部门Id，下面代码所实现的就是通过父级找出父级下所有相关的子级，包括儿子的儿子，儿子的儿子的儿子等。

代码：

**With** *SearchTree*  **as** (
--获取单位类别Id　递归的起始条件之一 
Select s.PMIS_SysOrganizationTypeId Id,'' As Pid,'{@Search['Type']}' Type,'OrgType' DataType
From PMIS_SysOrganizationType s 
Where PMIS_SysOrganizationTypeId='{@Search['Type']}'
Union all
--获取单位id　递归的起始条件之二
Select so.SysOrganizationId Id,(case when sor.ParentSysOrganizationId is null then so.[Type] else ParentSysOrganizationId end) Pid,'{@Search['Type']}' Type,'Org' DataType
From SysOrganization so 
left join SysOrgRelation sor on sor.ChildSysOrganizationId=so.SysOrganizationId
Where so.SysOrganizationId='{@Search['Type']}'
Union all
--获取部门id　递归的起始条件之三
Select sbu.SysBusinessUnitId ,(case when sur.ParentSysUnitId is null then suo.SysOrganizationId else sur.ParentSysUnitId end) Pid,'{@Search['Type']}' Type,'Unit' DataType
From SysBusinessUnit sbu 
Left join SysUnitOrg suo on suo.SysBusinessUnitId=sbu.SysBusinessUnitId
Left join SysUnitRelation sur on sur.ChildSysUnitId=sbu.SysBusinessUnitId
Where sbu.SysBusinessUnitId='{@Search['Type']}'
Union all 
--递归单位类别　递归过程一
Select s.PMIS_SysOrganizationTypeId,s.ParentId As Pid,'{@Search['Type']}','OrgType' DataType
From PMIS_SysOrganizationType s 
join SearchTree st on s.ParentId=st.id
Union all
--递归单位(父亲为类别时)　递归过程二
Select so.SysOrganizationId,so.[Type] Pid,'{@Search['Type']}','Org' DataType
From SysOrganization so 
inner join SearchTree st on st.Id=so.[Type]
Union all
--递归单位(父亲为上级单位时)　
Select so.SysOrganizationId,sor.ChildSysOrganizationId Pid,'{@Search['Type']}','Org' DataType 
From SysOrganization so 
join SysOrgRelation sor on sor.ChildSysOrganizationId=so.SysOrganizationId
inner join SearchTree st on  st.Id=sor.ParentSysOrganizationId

Union all
--单位递归部门(父亲为单位时)
Select sbu.SysBusinessUnitId, suo.SysOrganizationId  Pid,'{@Search['Type']}','Unit' DataType
From SysBusinessUnit sbu 
join SysUnitOrg suo on suo.SysBusinessUnitId=sbu.SysBusinessUnitId
--join SysUnitRelation sur on sur.ChildSysUnitId=sbu.SysBusinessUnitId
inner join SearchTree st on  suo.SysOrganizationId=st.id
--部门递归部门（父亲为上级部门）
Union all
Select sbu.SysBusinessUnitId,sur.ParentSysUnitId,'', 'Unit' DataType
From SysBusinessUnit sbu
join SysUnitRelation sur on sur.ChildSysUnitId=sbu.SysBusinessUnitId
--join SysUnitOrg suo on suo.SysBusinessUnitId=sbu.SysBusinessUnitId
Inner join SearchTree st on  sur.ParentSysUnitId=st.id

)

Select su.Number,su.FullName,st.[Type],su.Sex,su.MobilePhone,su.Degree,CONVERT(varchar(10),su.Birthday,120) Birthday,su.Description
  ,so.Caption,so.SysOrganizationId SysOrganizationId,su.SysUserId
  ,sbu.Caption, sbu.SysBusinessUnitId SysBusinessUnitId,st.DataType
From  SearchTree st
  Left join SysUnitOrg  suo on st.DataType='Org' and st.Id=suo.SysOrganizationId
  Left join SysUserUnit suu on st.DataType='Unit' and st.Id=suu.SysBusinessUnitId
  Left join SysBusinessUnit sbu on sbu.SysBusinessUnitId=suu.SysBusinessUnitId
  Left join SysUnitOrg s on s.SysBusinessUnitId=sbu.SysBusinessUnitId
  Left join SysOrganization so on so.SysOrganizationId=s.SysOrganizationId
  Inner join SysUser su on suu.SysUserId=su.SysUserId
Where suu.SysUserId is not null and IsNull(su.IsDeleted,'0')='0'
Order by su.CreatedOn