场景

业务需要，优惠券列表要求按类型进行排序，但是，类型并不是顺序的，即order by是解决不了问题的

建表

CREATE TABLE `custom_sort`  (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_german2_ci NOT NULL,
  `type` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `so`(`type`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_german2_ci ROW_FORMAT = Dynamic;

插入数据

INSERT INTO `custom_sort` VALUES (1, '刘一', 1);
INSERT INTO `custom_sort` VALUES (2, '陈二', 2);
INSERT INTO `custom_sort` VALUES (3, '张三', 3);
INSERT INTO `custom_sort` VALUES (4, '李四', 2);
INSERT INTO `custom_sort` VALUES (5, '王五', 5);
INSERT INTO `custom_sort` VALUES (6, '赵六', 0);
INSERT INTO `custom_sort` VALUES (7, '孙七', 7);

表结构数据

image

解决方案

field函数

SELECT * FROM `custom_sort` ORDER BY FIELd(type,1,3) desc,type

case when then

SELECT * FROM `custom_sort` ORDER BY CASE WHEN type= 3 THEN 0 WHEN type= 1 THEN 1 else 2 END ,type asc

查询结果

image