mysql自定义排序
场景
业务需要,优惠券列表要求按类型进行排序,但是,类型并不是顺序的,即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
查询结果
相关推荐
CoderToy 2020-11-16
emmm00 2020-11-17
王艺强 2020-11-17
ribavnu 2020-11-16
bianruifeng 2020-11-16
wangshuangbao 2020-11-13
苏康申 2020-11-13
vivenwan 2020-11-13
moyekongling 2020-11-13
云中舞步 2020-11-12
要啥自行车一把梭 2020-11-12
aydh 2020-11-12
kuwoyinlehe 2020-11-12
minerk 2020-11-12
vitasfly 2020-11-12
jazywoo在路上 2020-11-11
敏敏张 2020-11-11
世樹 2020-11-11