多列存储拒绝原因表关联描述表简化SQL查询方法咨询

简化方案1：关联子查询（绝大多数数据库通用，写法最简洁） #

不需要多次关联字典表，直接在查询字段中用子查询匹配对应原因描述，代码可读性更高：

select 
    account_no,
    resn_id1,
    (select resn_desc from reason_desc where resn_id = r.resn_id1) as resn_desc1,
    resn_id2,
    (select resn_desc from reason_desc where resn_id = r.resn_id2) as resn_desc2,
    resn_id3,
    (select resn_desc from reason_desc where resn_id = r.resn_id3) as resn_desc3,
    resn_id4,
    (select resn_desc from reason_desc where resn_id = r.resn_id4) as resn_desc4
from reject_reasons r;

如果REASON_DESC表的Resn_Id是主键/唯一索引，这个方案的查询性能和四次左连完全一致，代码更短更易维护。

简化方案2：行列转换（仅关联1次字典表，适合后续字段扩展场景） #

如果后续可能新增更多拒绝原因字段（比如Resn_Id5、Resn_Id6），可以先把列转行关联字典表，再行转列输出，只需要关联1次REASON_DESC：

select 
    account_no,
    max(case when rn=1 then resn_id end) resn_id1,
    max(case when rn=1 then resn_desc end) resn_desc1,
    max(case when rn=2 then resn_id end) resn_id2,
    max(case when rn=2 then resn_desc end) resn_desc2,
    max(case when rn=3 then resn_id end) resn_id3,
    max(case when rn=3 then resn_desc end) resn_desc3,
    max(case when rn=4 then resn_id end) resn_id4,
    max(case when rn=4 then resn_desc end) resn_desc4
from (
    select 
        r.account_no,
        t.rn,
        t.resn_id,
        rd.resn_desc
    from reject_reasons r
    cross apply (
        select 1 rn, r.resn_id1 resn_id from dual union all
        select 2 rn, r.resn_id2 resn_id from dual union all
        select 3 rn, r.resn_id3 resn_id from dual union all
        select 4 rn, r.resn_id4 resn_id from dual
    ) t
    left join reason_desc rd on t.resn_id = rd.resn_id
)
group by account_no
order by account_no;

以上是Oracle语法示例，MySQL 8.0+、PostgreSQL可替换dual部分实现相同逻辑。

原写法小优化 #

你当前用的是Oracle专属的(+)左连接老语法，换成标准SQL的LEFT JOIN写法兼容性更好，切换其他数据库不需要大幅修改：

select 
    r.account_no,
    r.resn_id1, rd1.resn_desc resn_desc1,
    r.resn_id2, rd2.resn_desc resn_desc2,
    r.resn_id3, rd3.resn_desc resn_desc3,
    r.resn_id4, rd4.resn_desc resn_desc4
from reject_reasons r
left join reason_desc rd1 on r.resn_id1 = rd1.resn_id
left join reason_desc rd2 on r.resn_id2 = rd2.resn_id
left join reason_desc rd3 on r.resn_id3 = rd3.resn_id
left join reason_desc rd4 on r.resn_id4 = rd4.resn_id;

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