Oracle SQL多表联查用LISTAGG时如何优先返回Closed类状态

实现思路 #

在聚合查询中增加优先级判断逻辑：

1. 先判断当前案件是否存在Closed开头的状态
2. 存在则直接返回对应的Closed类状态，忽略其余状态拼接
3. 不存在则保留原有LISTAGG逻辑拼接所有状态

改造后完整SQL #

select 
    c.case_id caseId, 
    c.last_name lastName, 
    c.first_name firstName,  
    case 
        -- 判断是否存在Closed类状态
        when max(case when lkp_cs.descr like 'Closed%' then 1 else 0 end) = 1 
        -- 存在则返回Closed类状态，多个Closed类状态可自定义取值规则
        then max(case when lkp_cs.descr like 'Closed%' then lkp_cs.descr end)
        -- 不存在则执行原有拼接逻辑
        else listagg(lkp_cs.descr,',') within group (order by lkp_cs.id) 
    end as caseStatus
from cases c 
join lkP_alt lkp_alt on c.ATL = lkp_alt.id
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
join users u ON (
    (c.created_by is not null and c.assigned_to is null and c.created_by = u.id) 
    or 
    (c.assigned_to is not null and c.assigned_to = u.id)
) 
left outer join users_unit uu on uu.user_id = u.id and c.delete_date is null
group by c.case_id, c.edipi, c.last_name, c.first_name

多Closed类状态适配说明 #

如果单个案件可能出现多个Closed类状态（如同时包含Closed和Closed Ext），可自定义优先级调整取值逻辑，示例如下：

-- 优先返回Closed，其次返回Closed Ext
substr(
    max(
        case 
            when lkp_cs.descr = 'Closed' then '1' || lkp_cs.descr 
            when lkp_cs.descr = 'Closed Ext' then '2' || lkp_cs.descr 
        end
    ), 2
)

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