For example I have in the table EMPLOYEE:
(code, name)
(1, 'Jimmy')
(2, 'Albert')
(3, 'Michelle')
(4, 'Felix' )
if you do: (select * from EMPLOYEE) you will get:
(1, 'Jimmy')
(2, 'Albert')
(3, 'Michelle')
(4, 'Felix' )
if you do: (select * from EMPLOYEE where code in (1,3,2,4) you will get:
(1, 'Jimmy')
(2, 'Albert')
(3, 'Michelle')
(4, 'Felix' )
How to get it in the order of CSV values in the IN clause, as is?
(1, 'Jimmy')
(3, 'Michelle')
(2, 'Albert')
(4, 'Felix' )
解决方案
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:
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