場景

業(yè)務(wù)需要，優(yōu)惠券列表要求按類型進(jìn)行排序，但是，類型并不是順序的，即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;

插入數(shù)據(jù)

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);

表結(jié)構(gòu)數(shù)據(jù)

image

解決方案

field函數(shù)

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

查詢結(jié)果

image