[问题收集]搜出同一对象同一渠道相隔5分钟的点并且值大于50

[MoXie]

原帖地址
http://topic.csdn.net/u/20071129/14/f14dbe38-0579-4750-ac55-2b95f01f1b42.html
------------------------------------------------------------------------------------------
提问者:w78z007
----------------------------
我有一个表有四个字段，对象，渠道，时间和值

例:a b 2007-11-29 12:20:00 50
a b 2007-11-29 12:25:00 80
a b 2007-11-29 12:35:00 80
d c 2007-11-29 12:40:00 90

要求：搜出同一对象同一渠道相隔5分钟的点并且值大于50。

那么搜出的结果应该如下:

a b 2007-11-29 12:20:00 50
a b 2007-11-29 12:25:00 80
------------------------------------------------------------------------------------------
回答者:SysTem128
----------------------------
mysql> 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');

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 1 row affected

Query OK, 1 row affected

Query OK, 1 row affected

Query OK, 1 row affected

mysql> 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 set

mysql> select VERSION();

+---------------+
| VERSION() |
+---------------+
| 5.0.18-nt-max |
+---------------+
1 row in set