需要按照函数中作为输入给出的顺序"in" sql Query的结果集，而无需任何创建和插入

Question

需要 SQL 查询的结果集，按照我在函数中给出的参数值的顺序排列。

mysql> select * from state where state_name in ("UP","AP","Orissa","MP");
+------------+
| state_name |
+------------+
| AP         |
| MP         |
| UP         |
| Orissa     |
+------------+

4 行（0.00 秒）

请提出一个解决方案，无需使用任何额外的插入和创建即可提供帮助。

Answer 1

select * from state where state_name in ("UP","AP","Orissa","MP")    
order by FIELD(state_name ,"UP","AP","Orissa","MP")

Answer 2

with ANSI sql case

select * from state where state_name in ("UP","AP","Orissa","MP")
order by case state_name 
         when "UP" then 1 
         when "AP" then 2 
         when "Orissa" then 3 
         when "MP" then 4 
         else 5 end;

Answer 3

老派：

SELECT
  State.*
FROM State
JOIN (SELECT 'UP' sn, '1' pos UNION ALL
      SELECT 'AP', '2' UNION ALL
      SELECT 'Orissa', '3' UNION ALL
      SELECT 'MP', '4') SortOrder
ON State.state_name = SortOrder.sn
ORDER BY SortOrder.pos;

SQL 小提琴