预言机保证顺序中的 IN 子句

Question

>我有SQL：

select name, city, age 
from Student where (name, city) 
in (('Tom1','CiTy1'),('Tom2','CiTy2'),...,('TomN','CiTyN'))

SQL保证订单的结果？

前任：

Name ----- City  ----- Age
Tom1   |   CiTy1   |   10
Tom2   |   CiTy1   |   17
Tom3   |   CiTy1   |   15
..............
TomN   |   CiTyN   |   09

Answer 1

您不能简单地使用以下排序，因为文本编号无法正确排序(很可能(：

ORDER BY city, name

但是我们可以删除文本组件，转换为 int，然后排序：

SELECT
    name, city, age
FROM Student
WHERE (name, city) In (('Tom1','CiTy1'), ('Tom2','CiTy2'),...,('TomN','CiTyN'))
ORDER BY
    CAST(REPLACE(city, 'CiTy') AS INT),
    CAST(REPLACE(name, 'Tom') AS INT)

Answer 2

正如其他人所说，您需要按子句添加顺序以保证排序。

如果您希望 in 子句确定顺序，您可以执行以下操作：

select name, city, age 
from Student where (name, city) 
in (('Tom1','CiTy1'),('Tom2','CiTy2'),...,('TomN','CiTyN'))
order by case when (name, city) in (('Tom1', 'CiTy1')) then 1
              when (name, city) in (('Tom2', 'CiTy2')) then 2
              ...
              when (name, city) in (('TomN', 'CiTyN')) then 99999999;

Answer 3

获得排序结果的唯一方法是使用 ORDER BY 。

您可以使用正则表达式删除任何非数字字符，将该字符串转换为数字，然后按该数字排序：

select name, city, age
from student
where ..
order by to_number(regexp_replace(name, '[^0-9]', '')), 
         to_number(regexp_replace(city, '[^0-9]', ''))