如何简化基于CTE统计不同Status和Division的SQL查询？

问题描述 #

我通过CTE统计数据，统计维度包含Status（WORKING、PENDING）和Division，但由于每个状态与部门的组合都需单独编写LEFT JOIN，导致SQL查询非常冗长，目前已编写10个LEFT JOIN来统计不同组合的数量。以下是完整的SQL查询：

declare @createdBy int=79

;with cte as (                
select max(w.WorkingNo)WorkingNo 
from                
working w 
join workingdealhistory wd on wd.WorkHistoryId=w.workingNo and 
w.status  IN ('WORKING','PENDING')     and w.mhlId>0  and w.IsActive=1
join TreasureTrove t on t.CandidateId=w.CandidateId and t.DepartmentId=2
group by w.CandidateId,w.status                
)   

select distinct m.potentialHospitalNo 
,m.hospital                       
,ph.clientname
,cdiwr.working cdiworking
,cdipn.pending cdipending
,himwr.working himworking
,himpn.pending himpending
,cmurwr.working cmurworking
,cmurpn.pending cmurpending                 
,odmwr.working odmworking
,odmpn.pending odmpending                   
,traumawr.working traumaworking
,traumapn.pending traumapending
,ph.ClientId
from PotentialHospitlMaster m (NOLOCK)
Inner JOIN HospitalStatus HS (NOLOCK) On m.potentialHospitalNo=HS.ClientId
inner join potentialhospital ph on ph.potentialhospitalno=m.potentialhospitalno
          
    --状态为'WORKING'且部门为'CDI'的统计         
LEFT join(select COUNT(w.WorkingNo)working, MHLId from cte c inner join
working w on w.WorkingNo=c.WorkingNo 
inner join WorkingDealHistory WH on w.WorkingNo=WH.WorkHistoryId                          
inner join potentialhospital phs on phs.potentialHospitalNo=w.MHLId where w.Status='working' and wh.Division='CDI' group by w.MHLId) as cdiwr on cdiwr.MHLId=ph.potentialHospitalNo 

    --状态为'PENDING'且部门为'CDI'的统计   
LEFT join(select COUNT(w.WorkingNo)pending, MHLId from cte c inner join
working w on w.WorkingNo=c.WorkingNo 
inner join WorkingDealHistory WH on w.WorkingNo=WH.WorkHistoryId                          
inner join potentialhospital phs on phs.potentialHospitalNo=w.MHLId where w.Status='pending' and wh.Division='CDI' group by w.MHLId) as cdipn on cdipn.MHLId=ph.potentialHospitalNo 

    --状态为'WORKING'且部门为'HIM'的统计
LEFT join(select COUNT(w.WorkingNo)working, MHLId from cte c inner join
working w on w.WorkingNo=c.WorkingNo 
inner join WorkingDealHistory WH on w.WorkingNo=WH.WorkHistoryId                          
inner join potentialhospital phs on phs.potentialHospitalNo=w.MHLId where w.Status='working' and wh.Division='HIM' group by w.MHLId) as himwr on himwr.MHLId=ph.potentialHospitalNo 

  --状态为'PENDING'且部门为'HIM'的统计
LEFT join(select COUNT(w.WorkingNo)pending, MHLId from cte c inner join
working w on w.WorkingNo=c.WorkingNo 
inner join WorkingDealHistory WH on w.WorkingNo=WH.WorkHistoryId                          
inner join potentialhospital phs on phs.potentialHospitalNo=w.MHLId where w.Status='pending' and wh.Division='HIM' group by w.MHLId) as himpn on himpn.MHLId=ph.potentialHospitalNo                     

--状态为'WORKING'且部门为'CMUR'的统计
LEFT join(select COUNT(w.WorkingNo)working, MHLId from cte c inner join
working w on w.WorkingNo=c.WorkingNo 
inner join WorkingDealHistory WH on w.WorkingNo=WH.WorkHistoryId                          
inner join potentialhospital phs on phs.potentialHospitalNo=w.MHLId where w.Status='working' and wh.Division='CMUR' group by w.MHLId) as cmurwr on cmurwr.MHLId=ph.potentialHospitalNo 

--状态为'PENDING'且部门为'CMUR'的统计
LEFT join(select COUNT(w.WorkingNo)pending, MHLId from cte c inner join
working w on w.WorkingNo=c.WorkingNo 
inner join WorkingDealHistory WH on w.WorkingNo=WH.WorkHistoryId                          
inner join potentialhospital phs on phs.potentialHospitalNo=w.MHLId where w.Status='pending' and wh.Division='CMUR' group by w.MHLId) as cmurpn on cmurpn.MHLId=ph.potentialHospitalNo                       

--状态为'WORKING'且部门为'ODM'的统计
LEFT join(select COUNT(w.WorkingNo)working, MHLId from cte c inner join
working w on w.WorkingNo=c.WorkingNo 
inner join WorkingDealHistory WH on w.WorkingNo=WH.WorkHistoryId                          
inner join potentialhospital phs on phs.potentialHospitalNo=w.MHLId where w.Status='working' and wh.Division='ODM' group by w.MHLId) as odmwr on odmwr.MHLId=ph.potentialHospitalNo 

--状态为'PENDING'且部门为'ODM'的统计
LEFT join(select COUNT(w.WorkingNo)pending, MHLId from cte c inner join
working w on w.WorkingNo=c.WorkingNo 
inner join WorkingDealHistory WH on w.WorkingNo=WH.WorkHistoryId                          
inner join potentialhospital phs on phs.potentialHospitalNo=w.MHLId where w.Status='pending' and wh.Division='ODM' group by w.MHLId) as odmpn on odmpn.MHLId=ph.potentialHospitalNo                          

--状态为'WORKING'且部门为'Trauma'的统计
LEFT join(select COUNT(w.WorkingNo)working, MHLId from cte c inner join
working w on w.WorkingNo=c.WorkingNo 
inner join WorkingDealHistory WH on w.WorkingNo=WH.WorkHistoryId                          
inner join potentialhospital phs on phs.potentialHospitalNo=w.MHLId where w.Status='working' and wh.Division='Trauma' group by w.MHLId) as traumawr on traumawr.MHLId=ph.potentialHospitalNo 

--状态为'PENDING'且部门为'Trauma'的统计
LEFT join(select COUNT(w.WorkingNo)pending, MHLId from cte c inner join
working w on w.WorkingNo=c.WorkingNo 
inner join WorkingDealHistory WH on w.WorkingNo=WH.WorkHistoryId                          
inner join potentialhospital phs on phs.potentialHospitalNo=w.MHLId where w.Status='pending' and wh.Division='Trauma' group by w.MHLId) as traumapn on traumapn.MHLId=ph.potentialHospitalNo 

where  m.IsActive=1 and HS.UpdatedStatus='MSA Sent' and HS.CreatedBy=@createdBy 
                    

请问能否通过GROUP BY或其他方式简化该查询？

简化方案 #

可以通过**条件聚合（CASE WHEN + COUNT）**替代多个重复的LEFT JOIN子查询，将所有统计逻辑合并到一个子查询中，大幅缩短代码长度，同时减少数据库的重复计算。

简化后的SQL代码 #

declare @createdBy int=79

;with cte as (                
    select max(w.WorkingNo) WorkingNo 
    from working w 
    join workingdealhistory wd on wd.WorkHistoryId = w.workingNo 
    join TreasureTrove t on t.CandidateId = w.CandidateId and t.DepartmentId = 2
    where w.status IN ('WORKING','PENDING') 
      and w.mhlId > 0  
      and w.IsActive = 1
    group by w.CandidateId, w.status                
),
stats_agg as (
    select 
        w.MHLId,
        -- 按部门+状态组合统计数量
        COUNT(case when w.Status = 'WORKING' and wh.Division = 'CDI' then w.WorkingNo end) as cdiworking,
        COUNT(case when w.Status = 'PENDING' and wh.Division = 'CDI' then w.WorkingNo end) as cdipending,
        COUNT(case when w.Status = 'WORKING' and wh.Division = 'HIM' then w.WorkingNo end) as himworking,
        COUNT(case when w.Status = 'PENDING' and wh.Division = 'HIM' then w.WorkingNo end) as himpending,
        COUNT(case when w.Status = 'WORKING' and wh.Division = 'CMUR' then w.WorkingNo end) as cmurworking,
        COUNT(case when w.Status = 'PENDING' and wh.Division = 'CMUR' then w.WorkingNo end) as cmurpending,
        COUNT(case when w.Status = 'WORKING' and wh.Division = 'ODM' then w.WorkingNo end) as odmworking,
        COUNT(case when w.Status = 'PENDING' and wh.Division = 'ODM' then w.WorkingNo end) as odmpending,
        COUNT(case when w.Status = 'WORKING' and wh.Division = 'Trauma' then w.WorkingNo end) as traumaworking,
        COUNT(case when w.Status = 'PENDING' and wh.Division = 'Trauma' then w.WorkingNo end) as traumapending
    from cte c 
    inner join working w on w.WorkingNo = c.WorkingNo 
    inner join WorkingDealHistory WH on w.WorkingNo = WH.WorkHistoryId                          
    inner join potentialhospital phs on phs.potentialHospitalNo = w.MHLId
    group by w.MHLId
)

select 
    m.potentialHospitalNo,
    m.hospital,
    ph.clientname,
    -- 直接引用聚合后的统计字段，空值替换为0
    isnull(sa.cdiworking, 0) as cdiworking,
    isnull(sa.cdipending, 0) as cdipending,
    isnull(sa.himworking, 0) as himworking,
    isnull(sa.himpending, 0) as himpending,
    isnull(sa.cmurworking, 0) as cmurworking,
    isnull(sa.cmurpending, 0) as cmurpending,
    isnull(sa.odmworking, 0) as odmworking,
    isnull(sa.odmpending, 0) as odmpending,
    isnull(sa.traumaworking, 0) as traumaworking,
    isnull(sa.traumapending, 0) as traumapending,
    ph.ClientId
from PotentialHospitlMaster m (NOLOCK)
Inner JOIN HospitalStatus HS (NOLOCK) On m.potentialHospitalNo = HS.ClientId
inner join potentialhospital ph on ph.potentialhospitalno = m.potentialhospitalno
left join stats_agg sa on sa.MHLId = ph.potentialHospitalNo
where m.IsActive = 1 
  and HS.UpdatedStatus = 'MSA Sent' 
  and HS.CreatedBy = @createdBy 
group by 
    m.potentialHospitalNo,
    m.hospital,
    ph.clientname,
    sa.cdiworking,
    sa.cdipending,
    sa.himworking,
    sa.himpending,
    sa.cmurworking,
    sa.cmurpending,
    sa.odmworking,
    sa.odmpending,
    sa.traumaworking,
    sa.traumapending,
    ph.ClientId

简化说明 #

1. 新增stats_agg CTE：将原来10个LEFT JOIN的统计逻辑合并到一个子查询中，通过CASE WHEN筛选对应状态和部门的记录，用COUNT统计有效记录数。
2. 替换多LEFT JOIN为单LEFT JOIN：主查询只需关联一次stats_agg，直接获取所有统计字段，避免重复关联相同的表，减少数据库负载。
3. 使用ISNULL处理空值：确保没有匹配记录时显示0而非NULL，保持结果数据的一致性。
4. 移除DISTINCT改用GROUP BY：原查询的DISTINCT可以通过主查询的GROUP BY替代，逻辑更清晰，同时避免重复数据。

内容的提问来源于stack exchange，提问作者coder rock