mysql自定义排序

  • Post author:
  • Post category:MySQL
  • Post comments:1评论

场景

业务需要,优惠券列表要求按类型进行排序,但是,类型并不是顺序的,即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

查询结果

Dylan

面朝大海·春暖花开

这篇文章有一个评论

  1. 第 TerrePousazy页

    By the technique, if you are creating an AI thesis, speak to other people: AI people have their own device of regulations. “… a popular scientist …” It matters not that said it or who performed it. In reality, such statements prejudice the viewers. Beware When Making use of “couple of, very most, all, any sort of, every”. If a paragraph says “The majority of computer bodies contain X”, you must be actually able to defend it. Are you sure you really understand the truths? The number of computers were actually constructed and sold last night? “must”, “always” “should” “verification”, “verify” Would a math wizzard concede that it is actually a verification? “program” Made use of in the feeling of “verify”

    [url=https://www.ukdissertations.net/dissertation-help/]dissertation help uk[/url]

发表评论