php - 按where条件排序

Question

我有以下查询:

select `state` from `table1` 
where `state` in ('NC','North Carolina','TN','Tennessee','CO','Colorado','NM','New Mexico','UT','Utah')
limit 200 offset 0

我需要按 state 排序的结果，因为它们出现在我的 where 子句中(即首先是 NC，然后是 TN，然后是 CO，依此类推)。

我该怎么做？我尝试使用 order by 但它没有给出这个结果。

Answer 1

你可以使用 ORDER BY FIELD

SELECT `state`
FROM `table1`
WHERE `state` IN ('NC','North Carolina','TN','Tennessee','CO','Colorado','NM','New Mexico','UT','Utah')
ORDER BY FIELD(`state`, 'NC','North Carolina','TN','Tennessee','CO','Colorado','NM','New Mexico','UT','Utah');

• SQLFiddle Demo