sql - SQL/mariadb 使用 IN() 以相同顺序在多个列上进行选择

Question

我需要 sql 中的语法，它以与 IN() 字段中给出的相同顺序返回结果。问题是主键由两个键组成。

所以以下内容不适用：

SELECT * FROM table where id IN (4,5,6) ORDER BY FIELD (id,4,5,6);

我的桌子：

primary(column 1+2)   column 3   column 4   cloumn 5......
    hash | num      |          |          |

用于理解的伪代码如下所示：

SELECT * FROM table WHERE (hash,num) IN (["fjdigjhu",5],["fgfjhgaa",2],...) ORDER BY FIELD ([hash,num],["fjdigjhu",5],["fgfjhgaa",2],...); 

标签： sqlmariadb

Answer 1

我会建议一个派生表：

SELECT t.*
FROM table t JOIN
     (SELECT 1 as ord, 'fjdigjhu' as hash, 5 as num UNION ALL
      SELECT 2 as ord, 'fgfjhgaa' as hash, 5 as num UNION ALL
      . . . 
     ) x
     USING (hash, num)
ORDER BY x.ord;