SQL查询需求：调整多表交易状态统计结果的输出格式

你只需要修改最外层的查询逻辑，将两个字段拼接为指定格式即可，同时可以根据使用的数据库调整隐藏表头的配置：

修改后的完整SQL（适配MySQL/PostgreSQL等支持CONCAT函数的数据库） #

select
concat(trx_status, ' - ', sum(trx_count))
from (
    select
    case
        when status = 'GR001' then 'GR001'
        when status = 'GR002' then 'GR002'
        when status = 'GR003' then 'GR003'
        when status = 'GR018' then 'GR018'
        when status = 'GR033' then 'GR033'
        when status = 'GR043' then 'GR043'
        when status = 'GR052' then 'GR052'
        when status = 'GR152' then 'GR152'
        when status = 'FR030' then 'FR030'
        when status = 'RM033' then 'RM033'
        when status = 'RM041' then 'RM041'
        when status = 'FR017' then 'FR017'
        when status = 'GR013' then 'GR013'
        when status = 'GR092' then 'GR092'
        when status = 'GR049' then 'GR049'
        when status = 'GR124' then 'GR124'
        when status = 'GR069' then 'GR069'
        when status = 'FR013' then 'FR013'
        when status = 'GR036' then 'GR036'
        when status = 'GR055' then 'GR055'
        when status not in ('GR001', 'GR002', 'GR003', 'GR018', 'GR033', 'GR043', 'GR052', 'GR152', 'FR030', 'RM033',  'RM041', 'FR017', 'GR013', 'GR092',  'GR049', 'GR124', 'GR069', 'FR013', 'GR036', 'GR055') then 'OTHERS'
    end as trx_status,
    count(status) as trx_count
    from receive_otp
    where created_at between '2021-10-09 00:00:00' and '2021-10-09 23:59:00'
    group by trx_status
    union all
    select
    case
        when status = 'GR001' then 'GR001'
        when status = 'GR002' then 'GR002'
        when status = 'GR003' then 'GR003'
        when status = 'GR018' then 'GR018'
        when status = 'GR033' then 'GR033'
        when status = 'GR043' then 'GR043'
        when status = 'GR052' then 'GR052'
        when status = 'GR152' then 'GR152'
        when status = 'FR030' then 'FR030'
        when status = 'RM033' then 'RM033'
        when status = 'RM041' then 'RM041'
        when status = 'FR017' then 'FR017'
        when status = 'GR013' then 'GR013'
        when status = 'GR092' then 'GR092'
        when status = 'GR049' then 'GR049'
        when status = 'GR124' then 'GR124'
        when status = 'GR069' then 'GR069'
        when status = 'FR013' then 'FR013'
        when status = 'GR036' then 'GR036'
        when status = 'GR055' then 'GR055'
        when status not in ('GR001', 'GR002', 'GR003', 'GR018', 'GR033', 'GR043', 'GR052', 'GR152', 'FR030', 'RM033',  'RM041', 'FR017', 'GR013', 'GR092',  'GR049', 'GR124', 'GR069', 'FR013', 'GR036', 'GR055') then 'OTHERS'
   end as trx_status,
    count(status) as trx_count
    from receive_obt
    where created_at between '2021-10-09 00:00:00' and '2021-10-09 23:59:00'
    group by trx_status
    union all
    select
    case
           when status = 'GR001' then 'GR001'
        when status = 'GR002' then 'GR002'
        when status = 'GR003' then 'GR003'
        when status = 'GR018' then 'GR018'
        when status = 'GR033' then 'GR033'
        when status = 'GR043' then 'GR043'
        when status = 'GR052' then 'GR052'
        when status = 'GR152' then 'GR152'
        when status = 'FR030' then 'FR030'
        when status = 'RM033' then 'RM033'
        when status = 'RM041' then 'RM041'
        when status = 'FR017' then 'FR017'
        when status = 'GR013' then 'GR013'
        when status = 'GR092' then 'GR092'
        when status = 'GR049' then 'GR049'
        when status = 'GR124' then 'GR124'
        when status = 'GR069' then 'GR069'
        when status = 'FR013' then 'FR013'
        when status = 'GR036' then 'GR036'
        when status = 'GR055' then 'GR055'
        when status not in ('GR001', 'GR002', 'GR003', 'GR018', 'GR033', 'GR043', 'GR052', 'GR152', 'FR030', 'RM033',  'RM041', 'FR017', 'GR013', 'GR092',  'GR049', 'GR124', 'GR069', 'FR013', 'GR036', 'GR055') then 'OTHERS'
    end as trx_status,
    count(status) as trx_count
    from receive_wlt
    where created_at between '2021-10-09 00:00:00' and '2021-10-09 23:59:00'
    group by trx_status
) as c
group by trx_status
order by trx_status;

补充说明 #

• 原SQL里的union替换成了union all，避免不必要的去重逻辑，提升查询性能。
• 新增了order by trx_status，输出结果会按状态码顺序排列，可读性更强。
• 如果是在MySQL命令行执行需要隐藏表头，执行命令时加上-N参数即可，示例：mysql -N -e "上述SQL语句" > 输出文件路径，最终输出内容完全符合要求的逐行格式。
• 其他数据库适配：Oracle将拼接逻辑改为trx_status || ' - ' || sum(trx_count)；SQL Server改为trx_status + ' - ' + cast(sum(trx_count) as varchar)即可。

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