如何按IN子句的顺序选择mysql行 [英] How to select mysql rows in the order of IN clause

问题描述

例如，我在表EMPLOYEE中具有:

For example I have in the table EMPLOYEE:

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

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

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

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

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

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

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

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

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

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

推荐答案

使用 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')

或使用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

这篇关于如何按IN子句的顺序选择mysql行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！