[大数据]Oracle查询结果根据in中的数据排序

遇到个SQL问题，查询单条数据：

希望的结果为：

即俩字段中数据位置需要一一对应（id为几，就对应几号）。

但实际的结果为：

俩字段中id和name的位置对应不上

具体SQL如下所示：（出问题的位置标红了）

select
m."pointName",
m."equCode",
m."equName",
regexp_substr(m.name, '[^,]+', 1, level) name,
m."itemsState",
regexp_substr(m."itemsState", '[^-]+', 1, level) "itemState",
m."checkPerson",
m."remark"
from
(select
PROJECT_ID "projectId",
check_person "checkPerson",
remark "remark",
items "items",
items_state "itemsState",
(select POINT_NAME from asmis_patrol_point where POINT_ID||'' = p.point_id ||'')"pointName",
(select FACILITY_ID from ASMIS_PURCHASE_RESOURCE_LEDGER where RESOURCE_LEDGER_ID = p.equipment_code) "equCode",
(select FACILITY_NAME from ASMIS_PURCHASE_RESOURCE_LEDGER where RESOURCE_LEDGER_ID = p.equipment_code) "equName" ,
(
SELECT listagg (item_name, ',') WITHIN GROUP (order by item_name) names
FROM asmis_check_item WHERE item_id in
(SELECT REGEXP_SUBSTR(p.items, '[^,]+', 1, LEVEL) FROM DUAL
CONNECT BY REGEXP_SUBSTR(p.items, '[^,]+', 1, LEVEL) IS NOT NULL )
) name
from
ASMIS_TASK_PROJECT p
where
p.TASK_ID = #{taskId}
order by
p.project_id
) m
connect by level <= regexp_count(m.name, ',') + 1
and m."projectId" = prior m."projectId"
and prior dbms_random.value is not null

这sql有点绕，根据实际查询单独理解下填红字部分其实就是下面这个样子

这段SQL就是根据in关键字里面的id去查询对应的name，name虽然查出来了，但name结果却不是按照3,5,4,2,1,6这么排序的，导致列转行后，name和id的位置对应不上，解决方法就是让这段sql查出来的name结果集，根据3,5,4,2,1,6这个顺序排就可以了，所以改一下这段sql中的order by关键字，换成order by?INSTR(字符串,排序字段)，字符串为in里面的数据

SELECT?

? ? listagg (name, ',') WITHIN GROUP (order by name) name

FROM

? ? asmis_check_item

WHERE

? ? item_id in?(3,5,4,2,1,6)

修改后为：

SELECT?

? ? listagg (name, ',') WITHIN GROUP (order by INSTR(‘3,5,4,2,1,6’,id)) name

FROM

? ? asmis_check_item

WHERE

? ? item_id in?(3,5,4,2,1,6)