mysql 查询结果返回顺序和in语句参数顺序一致

 

有时候我们需要按照in条件里的id顺序输出结果，可sql语句在不加order by的时候是按照asc排序的，下边的sql解决按照in条件顺序的id输出查询结果

mysql写法：

eg1：

SELECT * FROM EVENT WHERE eventId IN(443,419,431,440,420,414,509)  ORDER BY INSTR(',443,419,431,440,420,414,509,',CONCAT(',',eventId,','))

eg2：

SELECT

SUBJECT_ID AS subjectId,

QUESTION_ID AS questionId,

WRONG_TIMES AS wrongTimes

FROM

T_EXAM_WRONG_COLLECT

WHERE

BROKER_ID = '7795205'

AND TYPE = '1'

AND QUESTION_ID IN (285,249,261,262,188,212,281,282,283,284)

ORDER BY INSTR(',285,249,261,262,188,212,281,282,283,284,',CONCAT(',',QUESTION_ID,','))

 

oracle写法：

select name from order where oderid in（111,222,333,444,555,666）order by instr('111,222,333,444,555,666',orderid)

 

sqlserver写法：

Select * From Product Where id in (1,1588,15782,9887,54)  Order By charindex(','+ id +',', ',1,1588,15782,9887,54,')