SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for time
-- ----------------------------
CREATE TABLE `time` (
`id` int(11) NOT NULL auto_increment,
`object` text,
`channel` text,
`time` datetime default NULL,
`count` int(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `time` VALUES ('1', 'a', 'b', '2007-11-29 12:20:00', '50');
INSERT INTO `time` VALUES ('2', 'a', 'b', '2007-11-29 12:25:00', '80');
INSERT INTO `time` VALUES ('3', 'a', 'b', '2007-11-29 12:35:00', '80');
INSERT INTO `time` VALUES ('4', 'd', 'c', '2007-11-29 12:40:00', '90');以上内容应为你所提供,为方便其他人测试。
以下为现阶段测试语句.
select * from time a left join time b on b.time = DATE_ADD(a.time,INTERVAL 5 MINUTE) and a.object = b.object;
搞定……:)
忘了渠道……
-- ----------------------------
-- Table structure for time
-- ----------------------------
CREATE TABLE `time` (
`id` int(11) NOT NULL auto_increment,
`object` text,
`channel` text,
`time` datetime default NULL,
`count` int(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `time` VALUES ('1', 'a', 'b', '2007-11-29 12:20:00', '50');
INSERT INTO `time` VALUES ('2', 'a', 'b', '2007-11-29 12:25:00', '80');
INSERT INTO `time` VALUES ('3', 'a', 'b', '2007-11-29 12:35:00', '80');
INSERT INTO `time` VALUES ('4', 'd', 'c', '2007-11-29 12:40:00', '90');Query OK, 0 rows affectedQuery OK, 0 rows affectedQuery OK, 1 row affectedQuery OK, 1 row affectedQuery OK, 1 row affectedQuery OK, 1 row affectedmysql> select * from time a join time b on b.time = DATE_ADD(a.time,INTERVAL 5 MINUTE) and a.object = b.object and a.channel = b.channel and a.count>=50 and b.count>=50 ;
+----+--------+---------+---------------------+-------+----+--------+---------+---------------------+-------+
| id | object | channel | time | count | id | object | channel | time | count |
+----+--------+---------+---------------------+-------+----+--------+---------+---------------------+-------+
| 1 | a | b | 2007-11-29 12:20:00 | 50 | 2 | a | b | 2007-11-29 12:25:00 | 80 |
+----+--------+---------+---------------------+-------+----+--------+---------+---------------------+-------+
1 row in setmysql> select VERSION();+---------------+
| VERSION() |
+---------------+
| 5.0.18-nt-max |
+---------------+
1 row in set
一条记录会有2个id两个time两个……么?
如果需要提取独立的两条记录就得两条执行语句了,而且很难控制.