Oracle SQL：如何聚合嵌套查询计算出的同STATUS值行？

问题场景 #

在Oracle SQL中，通过嵌套查询内的CASE语句计算出STATUS列后，需要将相同STATUS值的行聚合并求和，但当前查询结果中相同STATUS的行分散显示（如Unknown、Rejected等均有多条记录），且不想重复编写冗长的CASE函数。

原查询语句 #

select 
(case 
             when stats.status = 'RJCT' AND stats.status_reason = 'NARR' AND stats.signing_status = 'SIGNED' THEN 'Rejected(error)/Unknown'
             when stats.status = 'RJCT' AND stats.status_reason != 'NARR' THEN 'Rejected'
             when stats.status = 'PDNG' AND stats.status_reason is null THEN 'Unknown'
             when stats.status = 'PDNG' AND stats.status_reason = 'NARR' and stats.signing_status = 'SIGNED' THEN 'onHold/PendingUserConfirmation'
             when stats.status = 'PDNG' AND stats.status_reason = 'DS0A' and stats.signing_status = 'SIGNED' THEN 'PartiallyConfirmed'
             when stats.status = 'PDNG' AND stats.signing_status = 'PENDING' THEN 'PendingConfirmation'
             when stats.status = 'PDNG' AND stats.signing_status = 'SIGNING' THEN 'PendingUserApproval'
             when stats.status = 'PDNG' AND stats.signing_status = 'CANCELLED' THEN 'UserApprovalCancelled'             
             when stats.status = 'PDNG' AND stats.signing_status = 'EXPIRED' THEN 'UserApprovalTimeout'             
             when stats.status = 'PDNG' AND stats.signing_status = 'FAILED' THEN 'UserApprovalFailed'
             when stats.status = 'PDNG' AND stats.signing_status = 'SIGNED' THEN 'Unknown'
             when stats.status = 'ACCP' THEN 'Paid'
             when stats.status = 'ACSP' AND stats.signing_status = 'PENDING' THEN 'PendingConfirmation'
             when stats.status = 'ACSP' AND stats.signing_status = 'SIGNED' THEN 'Confirmed'
             when stats.status = 'ACSP' AND stats.signing_status = 'EXPIRED' THEN 'UserApprovalTimeout'
             when stats.status = 'ACSP' AND stats.signing_status = 'FAILED' THEN 'UserApprovalFailed'
             when stats.status = 'ACWC' AND stats.status_reason = 'NARR' AND stats.signing_status = 'SIGNED' THEN 'Paid'
             when stats.status = 'ACWC' AND stats.status_reason = 'NARR' AND stats.signing_status = 'SIGNED' THEN 'Paid'
             when stats.status = 'ACWC' AND stats.status_reason != 'NARR' THEN 'PendingConfirmation'
             when stats.status = 'EXECUTING' AND stats.signing_status = 'SIGNED' THEN 'Confirmed'
             when stats.status = 'EXECUTING' AND stats.signing_status = 'PENDING' THEN 'Confirmed'
             when stats.status = 'FAILED' THEN 'UserApprovalFailed'
            ELSE 'UNKNOWN' END) 
as status, stats.count
from (select status, status_reason, signing_status, count(*) as count from payments
group by status, signing_status, status_reason) stats;

解决方案 #

无需重写CASE函数，只需在原查询外层增加一层聚合逻辑即可：

方法1：嵌套子查询外层聚合 #

SELECT status, SUM(count) AS total_count
FROM (
    SELECT 
        (case 
             when stats.status = 'RJCT' AND stats.status_reason = 'NARR' AND stats.signing_status = 'SIGNED' THEN 'Rejected(error)/Unknown'
             when stats.status = 'RJCT' AND stats.status_reason != 'NARR' THEN 'Rejected'
             when stats.status = 'PDNG' AND stats.status_reason is null THEN 'Unknown'
             when stats.status = 'PDNG' AND stats.status_reason = 'NARR' and stats.signing_status = 'SIGNED' THEN 'onHold/PendingUserConfirmation'
             when stats.status = 'PDNG' AND stats.status_reason = 'DS0A' and stats.signing_status = 'SIGNED' THEN 'PartiallyConfirmed'
             when stats.status = 'PDNG' AND stats.signing_status = 'PENDING' THEN 'PendingConfirmation'
             when stats.status = 'PDNG' AND stats.signing_status = 'SIGNING' THEN 'PendingUserApproval'
             when stats.status = 'PDNG' AND stats.signing_status = 'CANCELLED' THEN 'UserApprovalCancelled'             
             when stats.status = 'PDNG' AND stats.signing_status = 'EXPIRED' THEN 'UserApprovalTimeout'             
             when stats.status = 'PDNG' AND stats.signing_status = 'FAILED' THEN 'UserApprovalFailed'
             when stats.status = 'PDNG' AND stats.signing_status = 'SIGNED' THEN 'Unknown'
             when stats.status = 'ACCP' THEN 'Paid'
             when stats.status = 'ACSP' AND stats.signing_status = 'PENDING' THEN 'PendingConfirmation'
             when stats.status = 'ACSP' AND stats.signing_status = 'SIGNED' THEN 'Confirmed'
             when stats.status = 'ACSP' AND stats.signing_status = 'EXPIRED' THEN 'UserApprovalTimeout'
             when stats.status = 'ACSP' AND stats.signing_status = 'FAILED' THEN 'UserApprovalFailed'
             when stats.status = 'ACWC' AND stats.status_reason = 'NARR' AND stats.signing_status = 'SIGNED' THEN 'Paid'
             when stats.status = 'ACWC' AND stats.status_reason = 'NARR' AND stats.signing_status = 'SIGNED' THEN 'Paid'
             when stats.status = 'ACWC' AND stats.status_reason != 'NARR' THEN 'PendingConfirmation'
             when stats.status = 'EXECUTING' AND stats.signing_status = 'SIGNED' THEN 'Confirmed'
             when stats.status = 'EXECUTING' AND stats.signing_status = 'PENDING' THEN 'Confirmed'
             when stats.status = 'FAILED' THEN 'UserApprovalFailed'
            ELSE 'UNKNOWN' END) AS status,
        stats.count
    FROM (
        SELECT status, status_reason, signing_status, count(*) AS count 
        FROM payments
        GROUP BY status, signing_status, status_reason
    ) stats
) grouped_stats
GROUP BY status
ORDER BY status;

方法2：使用CTE（公共表表达式）提升可读性 #

WITH status_calculations AS (
    SELECT 
        (case 
             when stats.status = 'RJCT' AND stats.status_reason = 'NARR' AND stats.signing_status = 'SIGNED' THEN 'Rejected(error)/Unknown'
             when stats.status = 'RJCT' AND stats.status_reason != 'NARR' THEN 'Rejected'
             when stats.status = 'PDNG' AND stats.status_reason is null THEN 'Unknown'
             when stats.status = 'PDNG' AND stats.status_reason = 'NARR' and stats.signing_status = 'SIGNED' THEN 'onHold/PendingUserConfirmation'
             when stats.status = 'PDNG' AND stats.status_reason = 'DS0A' and stats.signing_status = 'SIGNED' THEN 'PartiallyConfirmed'
             when stats.status = 'PDNG' AND stats.signing_status = 'PENDING' THEN 'PendingConfirmation'
             when stats.status = 'PDNG' AND stats.signing_status = 'SIGNING' THEN 'PendingUserApproval'
             when stats.status = 'PDNG' AND stats.signing_status = 'CANCELLED' THEN 'UserApprovalCancelled'             
             when stats.status = 'PDNG' AND stats.signing_status = 'EXPIRED' THEN 'UserApprovalTimeout'             
             when stats.status = 'PDNG' AND stats.signing_status = 'FAILED' THEN 'UserApprovalFailed'
             when stats.status = 'PDNG' AND stats.signing_status = 'SIGNED' THEN 'Unknown'
             when stats.status = 'ACCP' THEN 'Paid'
             when stats.status = 'ACSP' AND stats.signing_status = 'PENDING' THEN 'PendingConfirmation'
             when stats.status = 'ACSP' AND stats.signing_status = 'SIGNED' THEN 'Confirmed'
             when stats.status = 'ACSP' AND stats.signing_status = 'EXPIRED' THEN 'UserApprovalTimeout'
             when stats.status = 'ACSP' AND stats.signing_status = 'FAILED' THEN 'UserApprovalFailed'
             when stats.status = 'ACWC' AND stats.status_reason = 'NARR' AND stats.signing_status = 'SIGNED' THEN 'Paid'
             when stats.status = 'ACWC' AND stats.status_reason = 'NARR' AND stats.signing_status = 'SIGNED' THEN 'Paid'
             when stats.status = 'ACWC' AND stats.status_reason != 'NARR' THEN 'PendingConfirmation'
             when stats.status = 'EXECUTING' AND stats.signing_status = 'SIGNED' THEN 'Confirmed'
             when stats.status = 'EXECUTING' AND stats.signing_status = 'PENDING' THEN 'Confirmed'
             when stats.status = 'FAILED' THEN 'UserApprovalFailed'
            ELSE 'UNKNOWN' END) AS status,
        stats.count
    FROM (
        SELECT status, status_reason, signing_status, count(*) AS count 
        FROM payments
        GROUP BY status, signing_status, status_reason
    ) stats
)
SELECT status, SUM(count) AS total_count
FROM status_calculations
GROUP BY status
ORDER BY status;

说明 #

两种方法均复用了原有的CASE计算逻辑，通过外层的GROUP BY status和SUM(count)实现相同STATUS行的合并求和，无需重复编写冗长的CASE语句。CTE方式相比嵌套子查询，代码结构更清晰，便于后续维护。

内容的提问来源于stack exchange，提问作者M. Jabulani