"select result.*,content.* from he_history_result result join he_history_content content on content.history_id = result.id "
"where result.create_time between date 00:00:00 and date 23:59:59 order by content.create_time limit 1"
select result.*,content.* from he_history_result result join he_history_content content on content.history_id = result.id where result.create_time between '2020-03-29 00:00::00' and '2020-03-29 23:59:59' order by content.create_time limit 1;
# 失败
select result.*,content.* from he_history_result result left join he_history_content content on result.id = (select content_in.history_id from he_history_content content_in where content_in.history_id = result.id order by content_in.create_time DESC limit 1) where result.create_time between '2020-03-29 00:00::00' and '2020-03-29 23:59:59';
# 成功
select result.*,content.* from he_history_result result left join he_history_content content on content.id = (select content_in.id from he_history_content content_in where content_in.history_id = result.id order by content_in.create_time DESC limit 1) where result.create_time between '2020-03-29 00:00::00' and '2020-03-29 23:59:59';
# 改造
select result.id as result_id,result.create_time as call_start_time,content.create_time as call_end_time,result.agent_id as agent_id,result.caller_id as caller_id,result.called_id as called_id,result.channel_id as channel_id from he_history_result result left join he_history_content content on content.id = (select content_in.id from he_history_content content_in where content_in.history_id = result.id order by content_in.create_time DESC limit 1) where result.create_time between '2020-03-29 00:00:00' and '2020-03-29 23:59:59';
实时建表语句
mysql> show create table he_history_result;
+-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| he_history_result | CREATE TABLE `he_history_result` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`create_time` datetime DEFAULT NULL,
`agent_id` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`called_id` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`caller_id` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`channel_id` varchar(255) COLLATE utf8_bin NOT NULL,
`pn` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`poi` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`short_vin` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`vin` varchar(255) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1802482 DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table he_history_content;
+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| he_history_content | CREATE TABLE `he_history_content` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`create_time` datetime DEFAULT NULL,
`confidence` int(11) DEFAULT NULL,
`index_num` int(11) DEFAULT NULL,
`result_code` int(11) DEFAULT NULL,
`role` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`sentence` text COLLATE utf8_bin,
`history_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `FK_755wl338xrm1fh8d1amspycle` (`history_id`),
CONSTRAINT `FK_755wl338xrm1fh8d1amspycle` FOREIGN KEY (`history_id`) REFERENCES `he_history_result` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=124915245 DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql -h 127.0.0.1 -u用户名 -p密码 --default-character-set=gb2312 -e "select * from 表名" 数据库名 > test.txt