mysql custom sort

mysql custom sort

Scenes

For business needs, the coupon list requires sorting by type, but the type is not sequential, that order byis, the problem cannot be solved

Build a table

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 data

INSERT INTO `custom_sort` VALUES (1,'刘一', 1);
INSERT INTO `custom_sort` VALUES (2,'Chen Er', 2);
INSERT INTO `custom_sort` VALUES (3,'Zhang San', 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);

Table structure data

solution

field function

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

search result

Reference: https://cloud.tencent.com/developer/article/1577743 mysql custom sorting-cloud + community-Tencent Cloud