英文:
Order By In Subquery
问题
如何在Oracle数据库中按升序获取子查询数据。因为在子查询中，ORDER BY 不起作用。
SELECT JSON_OBJECT(
'jobId' VALUE a.job_id,
'change_order' VALUE (
SELECT JSON_ARRAYAGG(
JSON_OBJECT(
'change_number' VALUE CHANGE_NBR,
'qwerty' VALUE (
SELECT JSON_ARRAYAGG(
JSON_OBJECT(
'samp' || CHANGE_NBR VALUE 'test' || CHANGE_NBR
)
RETURNING CLOB
)
FROM (
SELECT CHANGE_NBR
FROM builder_change_order_view
WHERE job_id = ${jobId}
AND rownum <= 4
ORDER BY CHANGE_NBR ASC
)
)
)
RETURNING CLOB
)
FROM builder_change_order_view
WHERE job_id = ${jobId}
AND rownum <= 4
)
)
这是示例输出，实际上，我希望在对象中按升序包含一个数组：{"change_number":1, "qwerty":[{"samp1":"test1"},{"samp2":"test2"},{"samp3":"test3"}]}。即，在升序中包含数组值({"":"", "array":[1,2,3,5]})。
英文:
HOW TO GET SUBQUERY DATA ASEC ORDER IN ORACLE DB. BECAUSE IN SUBQUERY ORDER BY IS NOT WORKING
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)
RETURNING CLOB)
from builder_change_order_view
where job_id =${jobId}
AND rownum < 4
ORDER BY change_nbr )
),
This is the sample output,
actually, I want an array in an object in ascending order {"change_number":1, "qwerty":[{"samp1": "test1"},{"samp2": "test2"},{"samp3": "test3"}]}.
ie, array values in ascending order({"":"", array:[1,2,3,5]})
答案1
得分: 2
你应该使用JSON_ARRAYAGG的自身ORDER BY子句：
JSON_ARRAYAGG(JSON_OBJECT(...) ORDER BY change_nbr)
请注意，你的ROWNUM过滤方式不正确。它在排序之前按任意的ROWNUM进行过滤，所以如果你只想汇总前3个值，你需要使用派生表：
SELECT JSON_ARRAY_AGG(JSON_OBJECT(...) ORDER BY change_nbr)
FROM (
SELECT *
FROM builder_change_order_view
WHERE job_id = ${jobId}
ORDER BY change_nbr
FETCH FIRST 3 ROWS ONLY
) t
英文:
You should use the JSON_ARRAYAGG's own ORDER BY clause:
JSON_ARRAYAGG(JSON_OBJECT(...) ORDER BY change_nbr)
Note that your ROWNUM filtering doesn't work correctly this way. It filters by arbitrary ROWNUM prior to ordering, so if you just want to aggregate the top 3 values, you'll have to use a derived table for this:
SELECT JSON_ARRAY_AGG(JSON_OBJECT(...) ORDER BY change_nbr)
FROM (
SELECT *
FROM builder_change_order_view
WHERE job_id = ${jobId}
ORDER BY change_nbr
FETCH FIRST 3 ROWS ONLY
) t
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论