层级sql

表一、在2020-11-30前（含11.30），符合条件的有效职位数明细，条件如下：
①    数据项：所属集团全称、所属企业全称、对应有效职位数、需求人数总数（若干=5）
②    企业范围：企业性质为国有企业、中央企业、央管协会单位

表二、在2020-08-10后（含8.10），符合条件的有效职位数明细，条件如下：
①    数据项：所属集团全称、所属企业全称、对应有效职位数、需求人数总数（若干=5）
②    企业范围：企业性质为国有企业、中央企业、央管协会单位

declare @#Tmp_Comp table (id UNIQUEIDENTIFIER, rootid UNIQUEIDENTIFIER, [level] int)
--获取集团公司
insert into @#Tmp_Comp (id, rootid, [level])
select CompanyId, CompanyId, 1
from Biz_Company
where companytype in ('GSXZ0103','GSXZ0113','GSXZ0114')
and IsGroupCompany = 1 and GroupCompanyId is not null

--获取一级子公司
insert into @#Tmp_Comp (id, rootid, [level])
select CompanyId, CT.id, 2
from Biz_Company C
    inner join @#Tmp_Comp CT on CT.id = C.ParentCompanyId and [level] = 1

--获取二级子公司
insert into @#Tmp_Comp (id, rootid, [level])
select C.CompanyId, CT.id, 3
from Biz_Company C
    inner join @#Tmp_Comp CT on CT.id = C.ParentCompanyId and [level] = 2

    
--select * from @#Tmp_Comp

select cr.CompanyName as N'所属集团全称',c.CompanyName as N'所属企业全称',count(cr.CompanyName) as N'有效职位数',sum( case when j.RecruitmentNum = 0 then 5 else j.RecruitmentNum  end ) as N'需求人数总数'  from @#Tmp_Comp t
inner join Biz_Company cr
on cr.CompanyId = t.rootid
inner join Biz_Company c
on c.CompanyId = t.id
inner join Biz_JobPosition j
on c.CompanyId = j.CompanyId
where j.status = 1 and j.auditstatus = 2
and j.ExpredTime >= '2020-11-30'
group by cr.CompanyName,c.CompanyName

select cr.CompanyName as N'所属集团全称',c.CompanyName as N'所属企业全称',count(cr.CompanyName) as N'有效职位数',sum( case when j.RecruitmentNum = 0 then 5 else j.RecruitmentNum  end ) as N'需求人数总数'  from @#Tmp_Comp t
inner join Biz_Company cr
on cr.CompanyId = t.rootid
inner join Biz_Company c
on c.CompanyId = t.id
inner join Biz_JobPosition j
on j.CompanyId = c.CompanyId 
where j.status = 1 and j.auditstatus = 2
and J.PublishTime >= '2020-08-10'
group by cr.CompanyName,c.CompanyName