如何实现两表STATUS非空时合并求和、空值时保留原记录的SQL查询

表结构及测试数据 #

create table pdc (
    acno varchar2(10),
    amt number,
    due_date date,
    status varchar2(2)
);

create table ovd (
    acno varchar2(10),
    amt number,
    due_date date,
    status varchar2(2)
);

insert into pdc values ('100',1000,to_date('01-jan-2023','dd-mon-yyyy'),'Z');
insert into pdc values ('100',1000,to_date('01-FEB-2023','dd-mon-yyyy'),'C');
insert into ovd values ('100',1000,to_date('01-mar-2023','dd-mon-yyyy'),'D');
insert into ovd values ('200',1000,to_date('01-apr-2023','dd-mon-yyyy'),'D');
insert into ovd values ('100',1000,to_date('01-may-2023','dd-mon-yyyy'),null);
insert into ovd values ('100',1000,to_date('01-jun-2023','dd-mon-yyyy'),null);
insert into pdc values ('100',1000,to_date('01-JUL-2023','dd-mon-yyyy'),NULL);

需求场景 #

• 当任意一张表或两张表中STATUS字段有值时，按ACNO汇总AMT的总和，并获取该账号对应的最大日期；
• 当STATUS字段为null时，每行记录需原样展示。

当前问题 #

现有SQL仅能处理STATUS非空的情况，无法获取STATUS为null的记录：

SELECT ACNO, SUM(AMT), MAX(DT) FROM (
SELECT ACNO, MAX(due_date) DT, SUM(AMT) AMT 
FROM PDC
WHERE NVL (STATUS, 'K') IN ('Z','C')
GROUP BY ACNO
UNION ALL
SELECT ACNO, MAX(due_date) DT, SUM(AMT) AMT
FROM OVD WHERE NVL (STATUS, 'K') IN ('D') GROUP BY ACNO)
GROUP BY ACNO ;

解决方案 #

要同时满足两种场景，需要将非空STATUS的汇总记录和空STATUS的明细记录通过UNION ALL合并，具体SQL如下：

-- 处理STATUS非空的汇总记录
SELECT acno, SUM(amt) AS total_amt, MAX(due_date) AS max_due_date
FROM (
    SELECT acno, amt, due_date FROM pdc WHERE status IS NOT NULL
    UNION ALL
    SELECT acno, amt, due_date FROM ovd WHERE status IS NOT NULL
)
GROUP BY acno
UNION ALL
-- 处理STATUS为空的明细记录
SELECT acno, amt AS total_amt, due_date AS max_due_date
FROM (
    SELECT acno, amt, due_date FROM pdc WHERE status IS NULL
    UNION ALL
    SELECT acno, amt, due_date FROM ovd WHERE status IS NULL
)
ORDER BY acno, max_due_date;

说明 #

1. 第一部分：合并两张表中STATUS非空的所有记录，按ACNO汇总AMT总和，并取该账号的最大到期日期；
2. 第二部分：合并两张表中STATUS为空的所有记录，直接原样输出每行数据，保证结果集结构与汇总部分一致；
3. 最后通过ORDER BY对结果按账号和日期排序，提升可读性。

预期结果 #

执行上述SQL后，会得到如下结果：

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