Sql语句如下select *********,faultcode
from summary where createTime
between (select date_sub(createTime,interval 50 second ) from summary where su_id='860') and (select date_sub(createTime,interval -50 second )from summary where su_id='860')
and faultcode<>(select faultcode from summary where su_id='860')建表语句如下DROP TABLE IF EXISTS `summary`;
CREATE TABLE `summary` (
`su_id` int(11) NOT NULL AUTO_INCREMENT,
`faultcode` varchar(255) DEFAULT NULL,
`createTime` datetime DEFAULT NULL,
PRIMARY KEY (`su_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;在红色*号地方填上一段语句,使结果
(su_id=860对应的faultcode为6611
满足sql语句查询的faultcode有5464,7585,7564,4234) faultcode(su_id=860对应的faultcode) faultcode(通过Sql语句查询出满足条件的faultcode)
6611 5464
6611 7585
6611 7564
6611 4234可以改写sql语句,只要是满足结果即可
from summary where createTime
between (select date_sub(createTime,interval 50 second ) from summary where su_id='860') and (select date_sub(createTime,interval -50 second )from summary where su_id='860')
and faultcode<>(select faultcode from summary where su_id='860')建表语句如下DROP TABLE IF EXISTS `summary`;
CREATE TABLE `summary` (
`su_id` int(11) NOT NULL AUTO_INCREMENT,
`faultcode` varchar(255) DEFAULT NULL,
`createTime` datetime DEFAULT NULL,
PRIMARY KEY (`su_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;在红色*号地方填上一段语句,使结果
(su_id=860对应的faultcode为6611
满足sql语句查询的faultcode有5464,7585,7564,4234) faultcode(su_id=860对应的faultcode) faultcode(通过Sql语句查询出满足条件的faultcode)
6611 5464
6611 7585
6611 7564
6611 4234可以改写sql语句,只要是满足结果即可
7585
7564
4234
对应着后面的faultcode
INSERT INTO `summary` VALUES (1,'66A9','2005-09-10 15:11:40');
INSERT INTO `summary` VALUES (2,'6075','2005-09-10 17:20:35');
INSERT INTO `summary` VALUES (3,'6168','2005-09-10 17:21:14');
INSERT INTO `summary` VALUES (4,'6A33','2005-09-10 17:21:14');
INSERT INTO `summary` VALUES (5,'697C','2005-09-10 17:21:14');
INSERT INTO `summary` VALUES (6,'6A59','2005-09-10 17:21:14');
INSERT INTO `summary` VALUES (7,'6A59','2005-09-10 17:21:14');
INSERT INTO `summary` VALUES (8,'6A59','2005-09-10 17:21:14');
INSERT INTO `summary` VALUES (9,'6792','2005-09-10 17:21:14');
INSERT INTO `summary` VALUES (10,'6793','2005-09-10 17:21:14');
INSERT INTO `summary` VALUES (11,'66AA','2005-09-10 17:21:14');
INSERT INTO `summary` VALUES (12,'697C','2005-09-10 17:21:15');
INSERT INTO `summary` VALUES (13,'6793','2005-09-10 17:21:15');
INSERT INTO `summary` VALUES (14,'6661','2005-09-10 17:21:16');
INSERT INTO `summary` VALUES (15,'6660','2005-09-10 17:21:16');
INSERT INTO `summary` VALUES (16,'6661','2005-09-10 17:21:17');
INSERT INTO `summary` VALUES (17,'6660','2005-09-10 17:21:17');
INSERT INTO `summary` VALUES (18,'6168','2005-09-10 17:21:19');
INSERT INTO `summary` VALUES (19,'63B2','2005-09-10 17:21:32');
INSERT INTO `summary` VALUES (20,'63A2','2005-09-10 17:21:32');
INSERT INTO `summary` VALUES (21,'6075','2005-09-10 17:22:05');
INSERT INTO `summary` VALUES (22,'6A59','2005-09-10 17:22:15');
INSERT INTO `summary` VALUES (23,'6A59','2005-09-10 17:22:15');
INSERT INTO `summary` VALUES (24,'6A59','2005-09-10 17:22:15');
INSERT INTO `summary` VALUES (25,'6A33','2005-09-10 17:22:49');
INSERT INTO `summary` VALUES (26,'66D6','2005-09-10 17:32:43');
INSERT INTO `summary` VALUES (27,'66D6','2005-09-10 17:33:41');
INSERT INTO `summary` VALUES (28,'66D6','2005-09-10 17:46:50');
INSERT INTO `summary` VALUES (29,'66D6','2005-09-10 17:46:57');
INSERT INTO `summary` VALUES (30,'66D6','2005-09-10 18:05:10');
INSERT INTO `summary` VALUES (31,'66D6','2005-09-10 18:06:11');
INSERT INTO `summary` VALUES (32,'66B0','2005-09-10 18:18:33');
INSERT INTO `summary` VALUES (33,'66D1','2005-09-10 18:18:40');
INSERT INTO `summary` VALUES (34,'66B0','2005-09-10 18:18:43');
INSERT INTO `summary` VALUES (35,'66D1','2005-09-10 18:18:44');
INSERT INTO `summary` VALUES (36,'66D5','2005-09-10 18:19:17');
INSERT INTO `summary` VALUES (37,'66D5','2005-09-10 18:19:20');
INSERT INTO `summary` VALUES (38,'684F','2005-09-10 18:21:10');
INSERT INTO `summary` VALUES (39,'684F','2005-09-10 18:21:18');
INSERT INTO `summary` VALUES (40,'684F','2005-09-10 18:35:20');
INSERT INTO `summary` VALUES (41,'66D6','2005-09-10 18:43:30');
INSERT INTO `summary` VALUES (42,'684F','2005-09-10 18:44:03');
INSERT INTO `summary` VALUES (43,'66D6','2005-09-10 18:44:31');
INSERT INTO `summary` VALUES (44,'697C','2005-09-10 18:52:01');
INSERT INTO `summary` VALUES (45,'697C','2005-09-10 18:52:01');
INSERT INTO `summary` VALUES (46,'697C','2005-09-10 18:52:01');
INSERT INTO `summary` VALUES (47,'697C','2005-09-10 18:52:01');
INSERT INTO `summary` VALUES (48,'697C','2005-09-10 20:58:50');
INSERT INTO `summary` VALUES (49,'697C','2005-09-10 20:58:50');
INSERT INTO `summary` VALUES (50,'697C','2005-09-10 20:58:50');
INSERT INTO `summary` VALUES (51,'697C','2005-09-10 20:58:50');
INSERT INTO `summary` VALUES (52,'66D6','2005-09-10 21:13:37');
INSERT INTO `summary` VALUES (53,'66D6','2005-09-10 21:13:42');
INSERT INTO `summary` VALUES (54,'682E','2005-09-10 21:28:14');
INSERT INTO `summary` VALUES (55,'6985','2010-06-07 19:46:24');
INSERT INTO `summary` VALUES (56,'6985','2010-06-07 19:46:24');
INSERT INTO `summary` VALUES (57,'6985','2010-06-07 19:46:24');
INSERT INTO `summary` VALUES (58,'6985','2010-06-07 19:46:24');
INSERT INTO `summary` VALUES (59,'6985','2010-06-07 19:46:27');
INSERT INTO `summary` VALUES (60,'6985','2010-06-07 19:46:27');
INSERT INTO `summary` VALUES (61,'6985','2010-06-07 19:46:27');
INSERT INTO `summary` VALUES (62,'6985','2010-06-07 19:46:27');
INSERT INTO `summary` VALUES (63,'6842','2010-06-07 19:47:07');
INSERT INTO `summary` VALUES (64,'6842','2010-06-07 19:47:07');
INSERT INTO `summary` VALUES (65,'6842','2010-06-07 19:47:07');
INSERT INTO `summary` VALUES (66,'6842','2010-06-07 19:47:07');
INSERT INTO `summary` VALUES (67,'6842','2010-06-07 19:47:07');
INSERT INTO `summary` VALUES (68,'6842','2010-06-07 19:47:07');
INSERT INTO `summary` VALUES (69,'6842','2010-06-07 19:47:08');
INSERT INTO `summary` VALUES (70,'6842','2010-06-07 19:47:08');
INSERT INTO `summary` VALUES (71,'6842','2010-06-07 19:47:08');
INSERT INTO `summary` VALUES (72,'6842','2010-06-07 19:47:08');
INSERT INTO `summary` VALUES (73,'6842','2010-06-07 19:47:08');
INSERT INTO `summary` VALUES (74,'6842','2010-06-07 19:47:08');
INSERT INTO `summary` VALUES (75,'6842','2010-06-07 19:47:08');
INSERT INTO `summary` VALUES (76,'6842','2010-06-07 19:47:08');
INSERT INTO `summary` VALUES (77,'6840','2010-06-07 19:48:44');
INSERT INTO `summary` VALUES (78,'6842','2010-06-07 19:48:44');
INSERT INTO `summary` VALUES (79,'6840','2010-06-07 19:48:44');
INSERT INTO `summary` VALUES (80,'6842','2010-06-07 19:48:44');
INSERT INTO `summary` VALUES (81,'6840','2010-06-07 19:48:44');
INSERT INTO `summary` VALUES (82,'6842','2010-06-07 19:48:44');
INSERT INTO `summary` VALUES (83,'6840','2010-06-07 19:48:44');
INSERT INTO `summary` VALUES (84,'6842','2010-06-07 19:48:44');
INSERT INTO `summary` VALUES (85,'6840','2010-06-07 19:48:44');
INSERT INTO `summary` VALUES (86,'6842','2010-06-07 19:48:44');
INSERT INTO `summary` VALUES (87,'6840','2010-06-07 19:48:44');
INSERT INTO `summary` VALUES (88,'6842','2010-06-07 19:48:44');
INSERT INTO `summary` VALUES (89,'691B','2010-06-07 19:52:56');
INSERT INTO `summary` VALUES (90,'691B','2010-06-07 19:52:56');
INSERT INTO `summary` VALUES (91,'691B','2010-06-07 19:52:56');
INSERT INTO `summary` VALUES (92,'691B','2010-06-07 19:52:56');
INSERT INTO `summary` VALUES (93,'691B','2010-06-07 19:52:56');
INSERT INTO `summary` VALUES (94,'691B','2010-06-07 19:52:56');
INSERT INTO `summary` VALUES (95,'691B','2010-06-07 19:52:56');
INSERT INTO `summary` VALUES (96,'691B','2010-06-07 19:52:56');
INSERT INTO `summary` VALUES (97,'691B','2010-06-07 19:52:56');
INSERT INTO `summary` VALUES (98,'691B','2010-06-07 19:52:56');
INSERT INTO `summary` VALUES (99,'691B','2010-06-07 19:52:56');
INSERT INTO `summary` VALUES (100,'691B','2010-06-07 19:52:56');
INSERT INTO `summary` VALUES (101,'691B','2010-06-07 19:52:56');
INSERT INTO `summary` VALUES (102,'691B','2010-06-07 19:52:56');
INSERT INTO `summary` VALUES (103,'691B','2010-06-07 19:52:57');
INSERT INTO `summary` VALUES (104,'691B','2010-06-07 19:52:57');
INSERT INTO `summary` VALUES (105,'691B','2010-06-07 19:52:57');
INSERT INTO `summary` VALUES (106,'691B','2010-06-07 19:52:57');
INSERT INTO `summary` VALUES (107,'691B','2010-06-07 19:52:57');
INSERT INTO `summary` VALUES (108,'691B','2010-06-07 19:52:57');
INSERT INTO `summary` VALUES (109,'691B','2010-06-07 19:52:58');
INSERT INTO `summary` VALUES (110,'691B','2010-06-07 19:52:58');
INSERT INTO `summary` VALUES (111,'691B','2010-06-07 19:52:58');
INSERT INTO `summary` VALUES (112,'691B','2010-06-07 19:52:58');
INSERT INTO `summary` VALUES (113,'691B','2010-06-07 19:52:58');
INSERT INTO `summary` VALUES (114,'691B','2010-06-07 19:52:58');
INSERT INTO `summary` VALUES (115,'691B','2010-06-07 19:52:58');
INSERT INTO `summary` VALUES (116,'6840','2010-06-10 10:05:58');
INSERT INTO `summary` VALUES (117,'6840','2010-06-10 10:05:58');
INSERT INTO `summary` VALUES (118,'6840','2010-06-10 10:05:58');
INSERT INTO `summary` VALUES (119,'6840','2010-06-10 10:05:58');
INSERT INTO `summary` VALUES (120,'6840','2010-06-10 10:05:58');
再提供一个66的测试结果
faultcode faultcode
6842 6985
6842 6985
由于提供的数据不全,结果或许不一样。
或者只提供一个思路即可
where A.su_id<>'860' and A.createtime between B.Createtime-interval 50 second and B.Createtime -interval -50 second
where A.su_id<>'860' and A.createtime between B.Createtime-interval 50 second and B.Createtime -interval -50 second Group by A.faultcode
结果中还是只显示一个faultcode,不显示su_id对应的faultcode
6985 是通过sql语句查询出来的
他的意思估计就是查询SU_id='66' 时,createtime-50 +50之间的faultcode数值。
yes...
where A.su_id<>'860' and A.createtime between B.Createtime-interval 50 second and B.Createtime -interval -50 second
Group by A.faultcode
FROM summary a WHERE SU_id='66'
AND (a.createTime BETWEEN a.createTime - INTERVAL 50 SECOND AND a.createTime + INTERVAL 50 SECOND)
GROUP BY a.faultcode
UNION ALL
SELECT faultcode
FROM summary WHERE createTime
BETWEEN (SELECT DATE_SUB(createTime,INTERVAL 50 SECOND ) FROM summary WHERE su_id='66') AND (SELECT DATE_SUB(createTime,INTERVAL -50 SECOND )FROM summary WHERE su_id='66')
AND faultcode<>(SELECT faultcode FROM summary WHERE su_id='66')
GROUP BY faultcode
where A.su_id<>'66' and A.createtime between B.Createtime-interval 50 second and B.Createtime -interval -50 second
好像看懂了,试一下这个。
WHERE A.su_id<>'66' AND A.createtime BETWEEN B.Createtime-INTERVAL 50 SECOND AND B.Createtime -INTERVAL -50 SECOND
AND a.faultcode<>b.faultcode
GROUP BY B.faultcode,A.faultcode
INSERT INTO `summary` VALUES (67,'6842','2010-06-07 19:47:07');
INSERT INTO `summary` VALUES (68,'6842','2010-06-07 19:47:07');
INSERT INTO `summary` VALUES (69,'6842','2010-06-07 19:47:08');
猜了大半天,自己都没讲要清除一样的faultcode