使用SELECT…IN(),如何对结果进行排序,使其与(…)列表中的顺序相匹配?

              their sort order
                       v     v     v
order by decode(id, 1, 1, 3, 2, 2, 3)
                    ^     ^     ^
              elements in IN list

顺序不适用于列表中的元素。

但是,您可以使用xmltable或collection来指定顺序。

with users(id, usr) as
(
select 1, 'Foo' from dual
union all select 2, 'Bar' from dual
union all select 3, 'Baz' from dual
)
select *
from users
join xmltable('1,3,2' columns id for ordinality, o int path'.' ) using (id)
order by o;

with users(id, usr) as
(
select 1, 'Foo' from dual
union all select 2, 'Bar' from dual
union all select 3, 'Baz' from dual
)
select *
from users
join (select rownum id, value(t) o from table(sys.odcinumberlist(1,3,2)) t) using (id)
order by o;

Collection iterator 按构造函数中指定的顺序返回元素。

因此,您依赖于集合迭代器的行为。

请注意,如果源行从1到n连续编号,则演示的方法效果良好。