Oracle分组查询如何过滤存在已关闭状态的案件整条记录

解决方案 #

方案1：使用HAVING聚合过滤（改动最小，推荐） #

你之前的写法仅过滤了状态为2的关联行，没有针对整个案件分组做全局判断，只需要在现有SQL的GROUP BY之后加HAVING条件，判断分组内是否存在状态为2的记录即可，修改后的完整语句如下：

select c.case_id caseId, c.memberId memberId, c.last_name lastName, c.first_name firstName, lkp_alt.descr altShowCauseAuthority, 
 'Active' as caseType,
 listagg(lkp_cs.descr,',') within group (order by lkp_cs.id) as caseStatus
 from cases c join lkP_alt_show_cause_authority lkp_alt
 on c.ATL_SHOW_CAUSE_AUTHORITY = lkp_alt.id
 and c.case_type = 'P'
 join case_status cs
 on cs.case_id = c.case_id
 join lkp_case_status lkp_cs
 on lkp_cs.id = cs.case_status_id 
 where (c.created_by = 1 and c.assigned_to is null)
 or (c.assigned_to = 1) and c.delete_date is null
 group by c.case_id, c.memberId, c.last_name, c.first_name, lkp_alt.descr, c.case_type
 -- 新增HAVING条件，排除存在关闭状态的案件
 having count(case when cs.case_status_id = 2 then 1 end) = 0;

逻辑说明：

• count(case when cs.case_status_id = 2 then 1 end) 会统计每个案件分组下状态为2的记录数，只要统计结果大于0就说明该案件已关闭，直接被过滤
• 不需要修改原有查询的关联逻辑和WHERE条件，兼容性最好

方案2：使用NOT EXISTS子查询 #

也可以通过子查询提前过滤掉所有已关闭的案件ID，再关联查询，写法如下：

select c.case_id caseId, c.memberId memberId, c.last_name lastName, c.first_name firstName, lkp_alt.descr altShowCauseAuthority, 
 'Active' as caseType,
 listagg(lkp_cs.descr,',') within group (order by lkp_cs.id) as caseStatus
 from cases c join lkP_alt_show_cause_authority lkp_alt
 on c.ATL_SHOW_CAUSE_AUTHORITY = lkp_alt.id
 and c.case_type = 'P'
 join case_status cs
 on cs.case_id = c.case_id
 join lkp_case_status lkp_cs
 on lkp_cs.id = cs.case_status_id 
 where (c.created_by = 1 and c.assigned_to is null)
 or (c.assigned_to = 1) and c.delete_date is null
 -- 新增NOT EXISTS条件，排除有已关闭记录的案件
 and not exists (
     select 1 from case_status cs2 
     where cs2.case_id = c.case_id 
     and cs2.case_status_id = 2
 )
 group by c.case_id, c.memberId, c.last_name, c.first_name, lkp_alt.descr, c.case_type;

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