数据表中有id,faultcode,createTime三个字段现在要求通过faultcode查询出,faultcode(故障代码)createTime(发生时间)前后10S内的其他faultcode现在有一点是某一个faultcode在数据表中不唯一,所以子查询返回很多行数据现在给出前面我写的几个版本给大家参考
select id, faultcode,createTime from faultdaily where faultcode<>'6A55'
and createTime between (select date_sub(createTime,interval 5 second )
from faultdaily where faultcode='6A55') and (select date_sub(createTime,interval -5 second ) from faultdaily where faultcode='6A55')
这个就是子查询返回很多行select id, faultcode,createTime from faultdaily f inner join
(select id, faultcode,createTime from faultdaily fa where faultcode<>'6A55') on(ABS(timediff(select date_sub(createTime,interval 5 second )
from faultdaily where faultcode='6A55',select date_sub(createTime,interval -5 second ) from faultdaily where faultcode='6A55'))<5)这个是别人给我的一个思路,但我写不下去了
select id, faultcode,createTime from faultdaily where faultcode<>'6A55'
and createTime between (select date_sub(createTime,interval 5 second )
from faultdaily where faultcode='6A55') and (select date_sub(createTime,interval -5 second ) from faultdaily where faultcode='6A55')
这个就是子查询返回很多行select id, faultcode,createTime from faultdaily f inner join
(select id, faultcode,createTime from faultdaily fa where faultcode<>'6A55') on(ABS(timediff(select date_sub(createTime,interval 5 second )
from faultdaily where faultcode='6A55',select date_sub(createTime,interval -5 second ) from faultdaily where faultcode='6A55'))<5)这个是别人给我的一个思路,但我写不下去了
语法有问题
try:
SELECT id, faultcode,createTime FROM faultdaily INNER JOIN
(SELECT id, faultcode,createTime FROM faultdaily WHERE faultcode<>'6A55') a
WHERE
(ABS(TIMEDIFF(
(SELECT DATE_SUB(createTime,INTERVAL 5 SECOND )
FROM faultdaily WHERE faultcode='6A55'),(SELECT DATE_SUB(createTime,INTERVAL -5 SECOND ) FROM faultdaily WHERE faultcode='6A55')
))<5);
DROP TABLE IF EXISTS `faultdaily`;
CREATE TABLE `faultdaily` (
`id` int(255) NOT NULL AUTO_INCREMENT,
`fd_car` varchar(20) CHARACTER SET gbk DEFAULT NULL,
`fd_cars` varchar(20) CHARACTER SET gbk DEFAULT NULL,
`fd_equipment` varchar(20) CHARACTER SET gbk DEFAULT NULL,
`carnumber` varchar(20) CHARACTER SET gbk DEFAULT '',
`faultcode` varchar(20) CHARACTER SET gbk DEFAULT '',
`faultstate` varchar(200) CHARACTER SET gbk DEFAULT '',
`createTime` varchar(20) CHARACTER SET gbk DEFAULT '0000-00-00',
`FfileTime` varchar(20) CHARACTER SET gbk DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=139143 DEFAULT CHARSET=utf8;
INSERT INTO faultdaily VALUES ('112', '46', '00', 'CCU1', '104846', '684F', 'No automatic train protection active', '2010-09-14 01:56:47', '2010-09-03');正确结果我也没有
应该是给你一个faultcode,表里面所有faultcode发生时间前后10秒的数据都会查询出来
set @T1=SELECT CREATETIME - INTERVAL 5 SECOND FROM faultdaily WHERE faultcode='123' ;
set @T2=SELECT CREATETIME + INTERVAL 5 SECOND FROM faultdaily WHERE faultcode='123' ;
select * from faultdaily where createtime between @T1 AND @T2;
这样查试试看。
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。