mysql - SQL ORDER BY 具有子订单的多个字段

Question

我正在尝试进行查询以获取按定义的类型、性别和姓名排序的学生类(class)列表。

我有下一个查询:

SELECT * FROM STUDENTS
  ORDER BY FIELD(type, 'b', 'p', 'j', 'i', 'a', 'c', 'v'), FIELD(sex, 'M', 'F'), `name` ASC

我得到了这个顺序，第一个类型，然后是该类型和名称的性别，但我需要一起定义一些类型和性别，如下所示:

type = 'b', sex = 'M', name
type = 'b', sex = 'F', name
type = 'p', sex = 'M', name
type = 'p', sex = 'F', name
type = 'i', sex = 'M', name
type = 'a', sex = 'M', name
type = 'c', sex = 'M', name
type = 'v', sex = 'M', name
type = 'j', sex = 'M', name
type = 'i', sex = 'F', name
type = 'a', sex = 'F', name
type = 'c', sex = 'F', name
type = 'v', sex = 'F', name
type = 'j', sex = 'F', name

Answer 1

您始终可以使用CASE语句来创建您想要的表达式

<强> SQL DEMO

SELECT *
FROM Table1
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 = 'F' THEN 4
              ....
              ELSE 9999
          END,
          name