如何按IN子句的顺序选择mysql行

For example I have in the table EMPLOYEE:

例如我在表EMPLOYEE中:

(code, name)
(1, 'Jimmy')
(2, 'Albert')
(3, 'Michelle')
(4, 'Felix' )

if you do: (select * from EMPLOYEE) you will get:

如果您这样做:(从EMPLOYEE中选择*)您将得到:

(1, 'Jimmy')
(2, 'Albert')
(3, 'Michelle')
(4, 'Felix' )

if you do: (select * from EMPLOYEE where code in (1,3,2,4) you will get:

如果您这样做:(从EMPLOYEE中选择*,您将获得(1,3,2,4)中的代码:

(1, 'Jimmy')
(2, 'Albert')
(3, 'Michelle')
(4, 'Felix' )

How to get it in the order of CSV values in the IN clause, as is?

如何按IN子句中的CSV值顺序获取它,原样如何?

(1, 'Jimmy')
(3, 'Michelle')
(2, 'Albert')
(4, 'Felix' )

2 个解决方案

#1

Use the FIND_IN_SET function:

使用FIND_IN_SET函数:

SELECT e.* 
  FROM EMPLOYEE e 
 WHERE e.code in (1,3,2,4) 
ORDER BY FIND_IN_SET(e.code, '1,3,2,4')

Or use a CASE statement:

或者使用CASE声明:

SELECT e.* 
  FROM EMPLOYEE e 
 WHERE e.code in (1,3,2,4) 
ORDER BY CASE e.code
           WHEN 1 THEN 1 
           WHEN 3 THEN 2
           WHEN 2 THEN 3
           WHEN 4 THEN 4
         END

#2

The general solution to this problem, retaining the order based on your input (CSV) file, is to add an AUTO_INCREMENT column to your table and order based on that. You probably will never display it as part of your query, but you can order on it to get the original order in your input file, after the import.

根据您的输入(CSV)文件保留订单的此问题的一般解决方案是向表中添加AUTO_INCREMENT列并根据该列进行排序。您可能永远不会将其显示为查询的一部分,但您可以在导入后在其上订购以获取输入文件中的原始订单。

#1