mysql特殊的排序

做项目的时候遇到特殊的排序，排序主要写sql语句进行排序。

需求：订单的状态有：1待支付 2待运输 3运输中 4已送达 5已签收 6已转寄 ，排序规则待支付>待运输>运输中,其余的按照创建时间进行排序，并且前面的三种状态也得需要根据创建时间倒叙。排序规则有四种：

1、SELECT id, order_state, create_date, CASE WHEN order_state = 1 THEN 1 WHEN order_state = 2 THEN 2 WHEN order_state = 3 THEN 3 ELSE 4 END AS sort FROM `tbn_order` ORDER BY sort ASC, create_date DESC;

2、SELECT id, order_state, create_date FROM `tb_order` ORDER BY ( CASE WHEN order_state = 1 THEN 1 WHEN order_state = 2 THEN 2 WHEN order_state = 3 THEN 3 ELSE 4 END ) ASC, create_date DESC;

3、SELECT t.id, t.order_state, t.order_create FROM tb_order t ORDER BY order_state = 2 DESC, order_state = 31 DESC, order_state = 5 DESC, t.order_create DESC;

4、SELECT t.id, t.order_state, t.order_create FROM tb_order t ORDER BY IF (t.order_state = 2, 0, 1), IF (t.order_state = 31, 0, 1), IF (t.order_state = 5, 0, 1), t.order_create DESC

标红的就是排序的规则。