如何通过多条件关联四张表获取ACNO与AMT记录？

• 若ACNO在LINK_MASTER表中有记录，按以下规则获取amt：
  • 若该ACNO在custfin表中的status为"2"，取customer表的amt字段；
  • 若该ACNO在custfin表中的status为"0"或"1"，取npa表的amt字段；
• 若ACNO在LINK_MASTER表中无记录，取customer表的amt字段；
• 若customer表中该ACNO的flag为'C'，则amt字段返回NULL。

表结构与数据（DDL/DML） #

create table link_master (
    acno varchar2(200)
);

create table custfin (
    status varchar2(200),
    acno varchar2(200)
);

create table npa (
    amt varchar2(200),
    acno varchar2(200)
);

create table customer (
    flag varchar2(200),
    amt varchar2(200),
    acno varchar2(200)
);

insert into link_master values('100');
insert into link_master values('101');
insert into link_master values('102');

insert into custfin values('1','101');
insert into custfin values('2','102');
insert into custfin values('0','100');

insert into npa values('3545','101');
insert into npa values('4566','102');
insert into npa values('4544','108');
insert into npa values('878','109');

insert into customer values('C','123','100');
insert into customer values('O','124','101');
insert into customer values('O','125','102');
insert into customer values('C','126','103');
insert into customer values('C','127','104');
insert into customer values('O','124','102');

需求说明 #

需编写可与其他查询关联的SELECT语句，仅输出acno和amt字段，符合上述业务规则。

解决方案 #

以下是严格遵循业务规则的SQL查询，支持与其他查询关联，覆盖所有可能的账号场景：

WITH all_acnos AS (
    -- 收集所有表中出现的唯一ACNO，避免遗漏账号
    SELECT acno FROM link_master
    UNION
    SELECT acno FROM custfin
    UNION
    SELECT acno FROM npa
    UNION
    SELECT acno FROM customer
)
SELECT 
    aa.acno,
    CASE
        -- 优先触发规则3：flag为'C'时返回NULL
        WHEN c.flag = 'C' THEN NULL
        -- 触发规则1：ACNO在LINK_MASTER中的分支
        WHEN lm.acno IS NOT NULL THEN
            CASE
                WHEN cf.status = '2' THEN c.amt
                WHEN cf.status IN ('0', '1') THEN n.amt
                ELSE NULL -- 处理custfin无对应记录的边界情况
            END
        -- 触发规则2：ACNO不在LINK_MASTER中的分支
        ELSE c.amt
    END AS amt
FROM all_acnos aa
-- 左连接保证无匹配记录时仍保留ACNO
LEFT JOIN link_master lm ON aa.acno = lm.acno
LEFT JOIN custfin cf ON aa.acno = cf.acno
LEFT JOIN npa n ON aa.acno = n.acno
LEFT JOIN customer c ON aa.acno = c.acno;

逻辑说明 #

1. CTE all_acnos：整合所有表中的账号，确保不会遗漏任何需要处理的ACNO；
2. LEFT JOIN关联：即使账号在部分表中无记录，也能被查询到并按规则处理；
3. 层级CASE判断：严格按照业务规则优先级执行，先处理flag为'C'的特殊情况，再区分LINK_MASTER的存在状态，最后匹配对应的amt来源。

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