MySQL按文本排序

Question

我知道如何按照id或数字排序，例如：

$getTicket = $sql->query("SELECT * FROM `ticket` 
                          WHERE `user`='$user->name' 
                          ORDER BY `id` DESC");

我在工单表中有一个状态(Status)字段，其中有三个选项:

1. 已回答(Answered)
2. 未回答(Unanswered)
3. 已完成(Done)

我想按照以下方式对其进行排序:

1. 未回答(Unanswered)
2. 已回答(Answered)
3. 已完成(Done)

有没有办法实现这个排序?

- Patric Nøis

---

重复的问题：首先按特定字段值排序。 - underscore_d

Answer 1

做简单的事情-

ORDER BY FIELD(Status, 'Unanswered', 'Answered', 'Done')

- Rohit Kumar

Answer 2

通常您可以使用case。

SELECT * FROM `ticket` 
WHERE `user` = '$user->name' 
ORDER BY case when Status = 'Unanswered' then 1
              when Status = 'Answered' then 2
              else 3
         end

或者是MySQL特定的find_in_set

SELECT * FROM `ticket` 
WHERE `user` = '$user->name' 
ORDER BY find_in_set(Status, 'Unanswered,Answered,Done')

- juergen d