mysql自定義排序

• 2020 年 1 月 19 日
• 筆記

場景

業務需要，優惠券列表要求按類型進行排序，但是，類型並不是順序的，即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);

表結構數據

解決方案

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

查詢結果