数据库有三字段,频道,时间和数值
我将数据库全部记录搜出来如下:频道名 时间 值
a 2007-11-27 14:45:00 9
a 2007-11-27 14:05:00 20
b 2007-11-27 14:20:00 18
b 2007-11-27 14:15:00 10
c 2007-11-27 14:25:00 19
c 2007-11-27 14:20:00 16
c 2007-11-27 14:10:00 6现在要求按条件显示,同一频道如果相临的两个时间点相差5分钟并且值大于9的或者值大于20的显示出来:要求得到的显示结果如下:
a 2007-11-27 14:05:00 20(满足单个值大于20)
b 2007-11-27 14:20:00 18 (满足相邻的点为5分钟且值大于9)
b 2007-11-27 14:15:00 10
c 2007-11-27 14:25:00 19
c 2007-11-27 14:20:00 16
我将数据库全部记录搜出来如下:频道名 时间 值
a 2007-11-27 14:45:00 9
a 2007-11-27 14:05:00 20
b 2007-11-27 14:20:00 18
b 2007-11-27 14:15:00 10
c 2007-11-27 14:25:00 19
c 2007-11-27 14:20:00 16
c 2007-11-27 14:10:00 6现在要求按条件显示,同一频道如果相临的两个时间点相差5分钟并且值大于9的或者值大于20的显示出来:要求得到的显示结果如下:
a 2007-11-27 14:05:00 20(满足单个值大于20)
b 2007-11-27 14:20:00 18 (满足相邻的点为5分钟且值大于9)
b 2007-11-27 14:15:00 10
c 2007-11-27 14:25:00 19
c 2007-11-27 14:20:00 16
select * from table t1 where t1.value>20 or
(
exists(
select * from table t2 where unix_timestamp(t1.time) - unix_timestamp(t2.time)=300)
)
and t1.value > 9
)
(
exists(
select * from t1 b where unix_timestamp(a.time) - unix_timestamp(b.time)=300
and a.name=b.name
)
and a.count > 9
)
三字段分别叫channel,t_time,value
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 join time b on b.time = DATE_ADD(a.time,INTERVAL 5 MINUTE) and a.object = b.object and a.count>=50 and b.count>=50 ;
-- 结果
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.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