Oracle数据库子查询排序失效：如何实现子查询数据升序排列？

问题分析 #

你的SQL中，ORDER BY change_nbr放在子查询末尾，但因为ROWNUM <4的执行顺序在排序之前，导致先随机取出3条数据再排序，无法得到按change_nbr升序排列的前3条数据；同时JSON_ARRAYAGG默认按数据检索顺序生成数组，原写法的排序逻辑无法作用到最终的JSON数组上。

解决方案 #

方法1（Oracle 12c及以上版本，推荐） #

先对数据按change_nbr升序排序，再取前3条，然后在JSON_ARRAYAGG中指定排序规则（双重保障）：

SELECT JSON_OBJECT(
    'jobId' VALUE a.job_id,
    'change_order' VALUE (
        SELECT JSON_ARRAYAGG( 
            JSON_OBJECT(
                'jobId' VALUE JOB_ID,
                'changeNbr' VALUE CHANGE_NBR,
                'changeDesc' VALUE CHANGE_DESC,
                'status' VALUE CHANGE_STATUS
                RETURNING CLOB
            ) ORDER BY CHANGE_NBR -- 聚合时指定排序
            RETURNING CLOB
        )
        FROM (
            SELECT * 
            FROM builder_change_order_view
            WHERE job_id = ${jobId}
            ORDER BY CHANGE_NBR -- 先排序再取前3条
            FETCH FIRST 3 ROWS ONLY
        )
    )
)
FROM your_main_table a -- 补充主表来源，原SQL中a表未指定
WHERE a.job_id = ${jobId}; -- 按需添加主表过滤条件

方法2（Oracle 12c以下版本） #

通过嵌套子查询先排序再取ROWNUM：

SELECT JSON_OBJECT(
    'jobId' VALUE a.job_id,
    'change_order' VALUE (
        SELECT JSON_ARRAYAGG( 
            JSON_OBJECT(
                'jobId' VALUE JOB_ID,
                'changeNbr' VALUE CHANGE_NBR,
                'changeDesc' VALUE CHANGE_DESC,
                'status' VALUE CHANGE_STATUS
                RETURNING CLOB
            ) ORDER BY CHANGE_NBR
            RETURNING CLOB
        )
        FROM (
            SELECT t.*
            FROM (
                SELECT * 
                FROM builder_change_order_view
                WHERE job_id = ${jobId}
                ORDER BY CHANGE_NBR
            ) t
            WHERE ROWNUM < 4
        )
    )
)
FROM your_main_table a
WHERE a.job_id = ${jobId};

关键说明 #

• ROWNUM是Oracle在数据检索时逐行分配的，必须先排序再过滤ROWNUM，才能得到排序后的前N条数据。
• 在JSON_ARRAYAGG中显式指定ORDER BY，可以确保生成的JSON数组严格按指定字段排序，不受数据检索顺序的影响。

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