多表关联查询需求：新增VIS_MESSAGE表本地金额与币种字段

现有查询及结果 #

当前已实现的查询语句：

select 
    opt.oper_type, oc.card_number, op.account_number,
    opt.oper_currency, opt.session_id, opt.part_key,
    opt.msg_type, op.auth_code, opt.oper_date, 
    opt.oper_amount, opt.oper_currency, op.customer_id, 
    clearing_sequence_num, clearing_sequence_count 
from 
    opr_operation opt, opr_participant op, opr_card oc 
where
    op.oper_id = opt.id
    and op.participant_type = 'PRTYISS'
    and oc.oper_id = opt.id
    and opt.msg_type in ('MSGTPAMC', 'MSGTPACC');

查询输出结果：

OPER_TYP    CARD_NUMBER ACCOUNT_NUMBER  oper_currency   opt.session_id  opt.part_key    opt.msg_type    op.auth_code    opt.oper_date   opt.oper_amount op.customer_id  clearing_sequence_num   clearing_sequence_count
1           12345678910234  12345678910234        1            12345    270324            MSG            123456           250324    3000             12345678                   1                  2 

需求说明 #

需要新增两个字段：

• 将VIS_MESSAGE表的oper_amount字段重命名为Local Amount
• 将VIS_MESSAGE表的oper_currency字段重命名为Local_Currency

关联条件：

• VIS_MESSAGE表的auth_code与op表的auth_code一致
• VIS_MESSAGE表的oper_date与opt表的oper_date一致

修正后的查询语句 #

推荐使用显式JOIN语法（可读性更强，避免隐式连接的歧义）：

select 
    opt.oper_type, oc.card_number, op.account_number,
    opt.oper_currency, opt.session_id, opt.part_key,
    opt.msg_type, op.auth_code, opt.oper_date, 
    opt.oper_amount, opt.oper_currency, op.customer_id, 
    clearing_sequence_num, clearing_sequence_count,
    vm.oper_amount as 'Local Amount',
    vm.oper_currency as 'Local_Currency'
from 
    opr_operation opt
join opr_participant op on op.oper_id = opt.id and op.participant_type = 'PRTYISS'
join opr_card oc on oc.oper_id = opt.id
left join VIS_MESSAGE vm on vm.auth_code = op.auth_code and vm.oper_date = opt.oper_date
where
    opt.msg_type in ('MSGTPAMC', 'MSGTPACC');

注：使用left join可保证即使VIS_MESSAGE中无匹配记录，原有查询结果依然保留；若仅需返回有匹配记录的数据，可替换为inner join。

期望输出结果 #

OPER_TYP    CARD_NUMBER ACCOUNT_NUMBER  oper_currency   opt.session_id  opt.part_key    opt.msg_type    op.auth_code    opt.oper_date   opt.oper_amount op.customer_id  clearing_sequence_num   clearing_sequence_count   Local Amount  Local Currency
1           12345678910234  12345678910234        1            12345    270324            MSG            123456           250324    3000             12345678                   1                  2            1234             2

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