按IN运算符中指定的特定顺序选择ID

时间:2022-02-15 11:15:51

I would like to try to select a certain set of numbers in a particular order, for use with loops.

我想尝试按特定顺序选择一组特定的数字,以便与循环一起使用。

SELECT ID 
FROM filter 
WHERE id in (87, 97, 117, 52, 240, 76, 141, 137, 157, 255, 186, 196, 133,
             175, 153, 224, 59, 205, 65, 47, 105, 80, 113, 293, 161, 145,
             192, 149, 231, 91, 101, 109, 215, 121, 125, 64, 41, 291, 367,
             388, 391, 462, 467) 

Doing this returns results sorted by ID, rather than in the order I specified. In most other similar questions a preferred answer was using CASE for particular entries, but what about selecting hundreds of records in a predetermined order?

这样做会返回按ID排序的结果,而不是按照我指定的顺序。在大多数其他类似问题中,首选答案是将CASE用于特定条目,但是如何按预定顺序选择数百条记录呢?

1 个解决方案

#1


4  

If you have hundreds of items, then use a derived table, such as:

如果您有数百个项目,则使用派生表,例如:

select f.id
from filter f join
     (values(1, 87), (2, 97), (3, 117), . . .) as v(ord, id)
     on f.id = v.id
order by ord;

#1


4  

If you have hundreds of items, then use a derived table, such as:

如果您有数百个项目,则使用派生表,例如:

select f.id
from filter f join
     (values(1, 87), (2, 97), (3, 117), . . .) as v(ord, id)
     on f.id = v.id
order by ord;