Java在一个表内,按照两个字段交叉排序,请问如何实现

小刘
  • 526

需要解决

在bulldozer_overview表中,有两个字段begin_timeentry_time,我希望按照这两个时间字段排序,有的时候begin_time是null有的时候entry_time是null

假如

idbegin_timeentry_time
101-01null
2null01-02
301-05null
4null01-04
501-03null

希望得到的结果是

idbegin_timeentry_time
101-05null
2null01-04
301-03null
4null01-02
501-01null

表结构和数据

-- ----------------------------
-- Table structure for bulldozer_overview
-- ----------------------------
DROP TABLE IF EXISTS `bulldozer_overview`;
CREATE TABLE `bulldozer_overview` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '车辆ID',
  `license_number` varchar(50) NOT NULL COMMENT '车牌号',
  `data_source` int(2) DEFAULT '0' COMMENT '数据来源',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `begin_time` varchar(50) DEFAULT NULL COMMENT '出发时间',
  `end_time` varchar(50) DEFAULT NULL COMMENT '到达时间',
  `elimination` varchar(50) DEFAULT NULL COMMENT '消纳地点',
  `rubbish_type` varchar(50) DEFAULT NULL COMMENT '垃圾类型',
  `order_number` varchar(100) DEFAULT NULL COMMENT '运单编号',
  `site_name` varchar(50) DEFAULT NULL COMMENT '工地名称',
  `site_area` varchar(20) DEFAULT NULL COMMENT '工地所属区',
  `elimination_area` varchar(20) DEFAULT NULL COMMENT '消纳地点所属区',
  `transport_name` varchar(50) DEFAULT NULL COMMENT '运输企业',
  `transport_area` varchar(20) DEFAULT NULL COMMENT '运输企业所属区',
  `transport_volume` float(10,2) DEFAULT NULL COMMENT '运输量(吨)',
  `driving_status` int(11) DEFAULT '2' COMMENT '车辆行驶状态',
  `entry_time` timestamp NULL DEFAULT NULL COMMENT '进消纳厂时间',
  `flag_delete` int(2) DEFAULT '0' COMMENT '删除标识',
  `driving_type` int(2) DEFAULT '0' COMMENT '行驶状态',
  `plant_name` varchar(100) DEFAULT NULL COMMENT '消纳厂名称',
  `license_path` varchar(300) DEFAULT NULL COMMENT '车牌抓拍图片路径',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=109 DEFAULT CHARSET=utf8 COMMENT='渣土车出入总览表';

-- ----------------------------
-- Records of bulldozer_overview
-- ----------------------------
BEGIN;
INSERT INTO `bulldozer_overview` VALUES (0, '京Q89HX3', 0, '2021-04-08 16:48:36', '2021-06-03 16:33:25', '2021-03-20 10:59:10', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, NULL, 0, 0, NULL, NULL);
INSERT INTO `bulldozer_overview` VALUES (1, '京MBE065', 0, '2021-04-08 16:48:36', '2021-06-03 16:33:29', '2021-03-21 10:59:10', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, NULL, 0, 0, NULL, NULL);
INSERT INTO `bulldozer_overview` VALUES (2, '京MFV786', 0, '2021-04-08 16:49:37', '2021-06-03 16:33:34', '2021-03-22 10:59:42', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, NULL, 0, 0, NULL, NULL);
INSERT INTO `bulldozer_overview` VALUES (3, '京CAB975', 0, '2021-04-08 16:49:37', '2021-06-03 16:30:15', '2021-03-24 10:59:10', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, NULL, 0, 0, NULL, NULL);
INSERT INTO `bulldozer_overview` VALUES (4, '京LAA101', 0, '2021-04-08 16:49:37', '2021-06-03 16:30:17', '2021-03-24 10:49:29', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, NULL, 0, 0, NULL, NULL);
INSERT INTO `bulldozer_overview` VALUES (5, '京LAA101', 0, '2021-06-03 16:30:30', '2021-06-03 16:30:37', '2021-03-24 10:49:29', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, NULL, 0, 0, NULL, NULL);
INSERT INTO `bulldozer_overview` VALUES (6, '京LAA101', 0, '2021-06-03 16:32:04', '2021-06-03 16:32:16', '2021-03-24 10:49:29', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, NULL, 0, 0, NULL, NULL);
INSERT INTO `bulldozer_overview` VALUES (7, '京LAA101', 0, '2021-06-03 16:32:55', '2021-06-04 11:41:48', '2021-03-24 10:49:29', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, NULL, 0, 0, NULL, NULL);
INSERT INTO `bulldozer_overview` VALUES (8, '京LAA101', 0, '2021-06-03 16:33:02', '2021-06-04 11:41:50', '2021-05-30 10:49:29', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, NULL, 0, 0, NULL, NULL);
INSERT INTO `bulldozer_overview` VALUES (9, '京LAA101', 0, '2021-06-03 16:34:12', '2021-06-04 11:41:51', '2021-05-01 10:49:29', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, NULL, 0, 0, NULL, NULL);
INSERT INTO `bulldozer_overview` VALUES (10, '京MFV786', 0, '2021-06-03 17:15:05', '2021-06-04 11:41:53', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, '2021-05-02 10:49:29', 0, 1, NULL, NULL);
INSERT INTO `bulldozer_overview` VALUES (11, '京MFV786', 0, '2021-06-03 17:15:09', '2021-06-04 11:41:55', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, '2021-05-31 10:49:29', 0, 1, NULL, NULL);
INSERT INTO `bulldozer_overview` VALUES (12, '京LAA101', 0, '2021-06-03 17:28:24', '2021-06-04 11:41:56', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, '2021-05-30 11:49:29', 0, 1, NULL, NULL);
INSERT INTO `bulldozer_overview` VALUES (13, '京A01', 0, '2021-06-03 18:03:27', '2021-06-04 11:41:57', '2021-06-01 10:49:29', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, NULL, 0, 0, NULL, NULL);
INSERT INTO `bulldozer_overview` VALUES (14, '京A01', 0, '2021-06-03 18:03:31', '2021-06-04 11:41:59', '2021-06-01 10:49:29', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, NULL, 0, 0, NULL, NULL);
INSERT INTO `bulldozer_overview` VALUES (15, '京A01', 0, '2021-06-03 18:03:36', '2021-06-04 11:42:00', '2021-06-01 10:49:29', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, NULL, 0, 0, NULL, NULL);
INSERT INTO `bulldozer_overview` VALUES (16, '京A01', 0, '2021-06-03 18:03:39', '2021-06-04 11:42:02', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, '2021-06-01 10:49:29', 0, 1, NULL, NULL);
INSERT INTO `bulldozer_overview` VALUES (17, '京A01', 0, '2021-06-03 18:03:42', '2021-06-04 11:42:03', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, '2021-06-01 10:49:29', 0, 1, NULL, NULL);
INSERT INTO `bulldozer_overview` VALUES (18, '京B02', 0, '2021-06-03 18:06:50', '2021-06-04 11:42:06', '2021-06-01 10:49:29', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, NULL, 0, 0, NULL, NULL);
INSERT INTO `bulldozer_overview` VALUES (19, '京A01', 0, '2021-06-03 19:15:37', '2021-06-04 11:42:08', '2021-06-02 10:49:29', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, NULL, 0, 0, NULL, NULL);
INSERT INTO `bulldozer_overview` VALUES (20, '京LAA101', 0, '2021-06-03 23:46:43', '2021-06-04 11:42:11', '2021-03-24 10:49:29', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, NULL, 0, 0, NULL, NULL);
回复
阅读 594
1 个回答
SELECT *
FROM `bulldozer_overview`
ORDER BY IF(ISNULL(`begin_time`), `entry_time`, `begin_time`) DESC;
撰写回答
你尚未登录,登录后可以
  • 和开发者交流问题的细节
  • 关注并接收问题和回答的更新提醒
  • 参与内容的编辑和改进,让解决方法与时俱进
你知道吗?

宣传栏