如何调整SQL查询结果列顺序：Transaction_Type移至次列、OPER_TYPE移至末列

要实现你需要的列顺序调整，直接修改SELECT子句中列的声明位置即可，具体修改后的完整SQL如下：

select 
            oc.card_number,
            -- 把Transaction_Type移到第二列
            case 
                opt.oper_type when 'OPTP0000' then '05-PURCHASE' 
                            when 'OPTP0020' then '06-CREDIT VOUCHER' 
                            else 'other' end as Transaction_Type,
            opt.oper_date as "TRANSACTION_DATE", 
            op.account_number, 
            case
                vf.sttl_currency when '840' then 'USD' end as "SOURCE_AMOUNT_CURRENCY", 
            case 
                opt.oper_type when 'OPTP0000' then  -vf.sttl_amount
                          else  vf.sttl_amount end as "SOURCE_AMOUNT",
            op.auth_code, 
            opt.mcc, 
            opt.terminal_number as "TERMINAL_ID", 
            vf.pos_terminal_cap, 
            vf.pos_entry_mode, 
            opt.part_key as "CAPTURE_DATE", 
            opt.oper_amount as "TRANSACTION_AMOUNT",
            opt.oper_currency as "TRANSACTION_CURRENCY",
            opt.merchant_name,
            nvl(cc.visa_country_code,opt.merchant_country) merchant_country,
            opt.clearing_sequence_num, 
            opt.clearing_sequence_count,
            -- 把OPER_TYPE移到最后一列
            opt.oper_type
     from 
      opr_operation opt 
       INNER JOIN opr_participant op
       ON op.oper_id = opt.id
       INNER JOIN opr_card oc
       ON oc.oper_id = opt.id
       INNER JOIN vis_fin_message vf
       ON     opt.id = vf.id
       INNER JOIN  com_country cc ON cc.code= opt.merchant_country 
          AND op.card_id = vf.card_id
      WHERE  opt.clearing_sequence_num > 1
      AND    opt.part_key >= DATE '2024-03-01'
      AND    opt.msg_type IN ('MSGTPAMC', 'MSGTPACC')
      AND    op.participant_type = 'PRTYISS'

关键改动说明： #

1. 原本在SELECT末尾的Transaction_Type计算逻辑，被移到了oc.card_number之后，成为第二列
2. 原本在中间位置的opt.oper_type，被移到了SELECT子句的最后一行

SQL查询的结果列顺序完全由SELECT子句中列的书写顺序决定，这样调整后就能得到你期望的输出列排列。

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