通过多个字段订购

Question

我在顺序上有此查询：

ORDER BY FIELD(type, 'b', 'p', 'j', 'i', 'a', 'c', 'v'), 
         FIELD(sex, 'M', 'F'), 
         user.name ASC";

，但我需要下一个订单的结果：

所以我解释了错误，我想我对此太厌倦了。我需要得到这样的东西。

order by (case 
when type = 'b' and sex = 'M' then 1
when type = 'b' and sex = 'F' then 2
when type = 'p' and sex = 'M' then 3
when type = 'p' and sex = 'H' then 4
when type = 'i' and sex = 'M' then 5
when type = 'a' and sex = 'M' then 6
when type = 'c' and sex = 'M' then 7
when type = 'v' and sex = 'M' then 8
when type = 'j' and sex = 'M' then 9
when type = 'i' and sex = 'H' then 10
when type = 'a' and sex = 'H' then 11
when type = 'c' and sex = 'H' then 12
when type = 'v' and sex = 'H' then 13
when type = 'j' and sex = 'H' then 14               
end) asc, name asc;

但是此代码不起作用。这是我得到的结果：

 | a | F |
 | a | F |
 | a | M |
 | v | M |
 | j | M |

有什么想法使查询清楚吗？

Answer 1

这可以使用Radim提到的情况下的顺序来实现：

order by (case when type = 'b' and sex = 'M' then 1
              when type = 'b' and sex = 'F' then 2
              when type = 'p' and sex = 'M' then 3
          end) asc,name asc;

这只是您可以在子句中进一步添加剩余条件。

Answer 2

select * from t
  order by case when type = 'b' then 1
                when type = 'p' then 2
                when type = 'j' then 3
                when type = 'i' then 4
                when type = 'a' then 5
                when type = 'c' then 6
                when type = 'v' then 7
           end,
           case when sex = 'M' then 1
                when sex = 'F' then 2
           end,
           name;