如何将指定SQL查询得到的行数据在输出中转为列格式

需求说明 #

需要将现有SQL查询返回的多批次运行结果，转换为每个统计日期仅保留最新一条运行记录的格式，原SQL如下：

select subs_nm, as_of_date, run_status, (select max (tp.pr_vl)
from ual_mng.tqueue tq, ual_mng.tparams tp, ual_mng.tstatus ts 
WHERE tq.tid = tp.tid AND tq.tid = ts.tid and tq.run_id = pcm.run_id and tp.pr_nm in ('TOT_RECORD_CNT')) as RECORD_COUNT 
from UAL_MNG.PCM_SUBS_RUN_DTL_VW pcm where SUBS_NM='S_TS2_AQUA_A1_RLAP_DL' and AS_OF_DATE in ('2021-09-01','2021-09-02') order by run_start_dtm desc;

解决方法 #

可以直接使用Oracle内置的窗口函数改造现有SQL，无需调整原有业务逻辑，修改后的语句如下：

SELECT subs_nm, as_of_date, run_status, RECORD_COUNT
FROM (
    SELECT 
        pcm.subs_nm, 
        pcm.as_of_date, 
        pcm.run_status, 
        (
            SELECT MAX(tp.pr_vl)
            FROM ual_mng.tqueue tq, ual_mng.tparams tp, ual_mng.tstatus ts 
            WHERE tq.tid = tp.tid 
              AND tq.tid = ts.tid 
              AND tq.run_id = pcm.run_id 
              AND tp.pr_nm = 'TOT_RECORD_CNT'
        ) AS RECORD_COUNT,
        ROW_NUMBER() OVER (PARTITION BY pcm.as_of_date ORDER BY pcm.run_start_dtm DESC) AS rn
    FROM UAL_MNG.PCM_SUBS_RUN_DTL_VW pcm 
    WHERE pcm.SUBS_NM = 'S_TS2_AQUA_A1_RLAP_DL' 
      AND pcm.AS_OF_DATE IN ('2021-09-01','2021-09-02')
) t
WHERE rn = 1;

逻辑说明 #

• 内层查询新增ROW_NUMBER()窗口函数，按as_of_date字段分组，每组内按运行开始时间run_start_dtm倒序排序，生成行号标记rn
• 外层查询仅保留rn=1的记录，即每个统计日期对应的最新一次运行结果
• 原有获取总记录数的子查询逻辑完全保留，不会影响业务统计结果

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