sql之排序

sql之排序

排序

• 创建插入

  CREATE TABLE `test1` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `name` varchar(50) NOT NULL,
    `date_time` datetime NOT NULL,
    `status` int(5) NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
  INSERT INTO `test1` VALUES
  (NULL, '测试1', '2018-03-05 11:09:00', 1),(NULL, '测试2', '2018-03-06 11:09:00', 1),(NULL, 'abc', '2018-03-07 11:09:00', 1), 
  (NULL, 'def', '2018-04-08 11:09:00', 2),(NULL, '李某某', '2018-04-17 11:09:00', 1),(NULL, '饭某某', '2018-04-20 13:09:00', 2),
  (NULL, '赵', '2018-04-20 01:09:00', 4),(NULL, '倩', '2018-04-28 11:09:00', 2),(NULL, 'andy', '2018-04-30 11:09:00', 1),
  (NULL, 'tony', '2018-05-08 11:09:00', 4),(NULL, 'tom', '2018-05-07 11:09:00', 3),(NULL, 'bill', '2018-05-18 11:09:00', 3),
  (NULL, 'james', '2018-06-07 11:09:00', 4),(NULL, 'anthony', '2018-06-18 11:09:00', 2),(NULL, '盖茨', '2018-04-21 11:09:00', 1),
  (NULL, '部长', '2018-04-24 11:09:00', 4),(NULL, '李总', '2018-04-20 11:09:00', 5),(NULL, '张总', '2018-04-29 11:09:00', 2),
  (NULL, '王总', '2018-04-19 11:09:00', 3),(NULL, '唐总', '2018-05-01 11:09:00', 2);
  

• 单列排序

  select * from test1 order by date_time;--默认升序
  

• 多列排序

  SELECT * FROM test1 ORDER BY `status`, date_time DESC
  

• 自定义排序

  order by field(value,str1,str2,str3,str4,,,,,,strn)
  

  其中value后面的参数自定义，不限制参数个数,将获取出来的数据根据str1,str2,str3,str4等的顺序排序

  select * from driver_log order by field(name,'Suzi','Ben','Henry');  
  

​ 在原来数据的基础上，对满足条件的记录排序，其他记录位置相对不变