Heim >Backend-Entwicklung >PHP-Tutorial >求同一列不同条件形成的两行的时间差的sql语句
表
CREATE TABLE `hp_report` ( `id` int(10) unsigned NOT NULL auto_increment,`code` varchar(255) NOT NULL, `content` mediumtext NOT NULL, `ctime` datetime NOT NULL,) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=662555 ;INSERT INTO `hp_report` VALUES (2, 'a', 'on', '2014-07-04 21:17:53');INSERT INTO `hp_report` VALUES (3, 'a', 'abc', '2014-07-04 21:18:53');INSERT INTO `hp_report` VALUES (4, 'a', 'off', '2014-07-04 21:19:53');INSERT INTO `hp_report` VALUES (5, 'b', 'on', '2014-07-04 21:20:53');INSERT INTO `hp_report` VALUES (6, 'b', 'abc', '2014-07-04 21:22:53');INSERT INTO `hp_report` VALUES (7, 'b', 'off', '2014-07-04 21:29:53');INSERT INTO `hp_report` VALUES (8, 'a', 'on', '2014-07-04 21:34:53');INSERT INTO `hp_report` VALUES (9, 'a', 'abc', '2014-07-04 21:36:53');INSERT INTO `hp_report` VALUES (10, 'a', 'off', '2014-07-04 21:45:53');INSERT INTO `hp_report` VALUES (11, 'b', 'on', '2014-07-04 22:12:53');INSERT INTO `hp_report` VALUES (13, 'b', 'abc', '2014-07-04 22:18:53');INSERT INTO `hp_report` VALUES (14, 'b', 'off', '2014-07-04 22:19:53');
INSERT INTO `hp_report` VALUES (4, 'a', 'off', '2014-07-04 21:19:53');和INSERT INTO `hp_report` VALUES (2, 'a', 'on', '2014-07-04 21:17:53');之间ctime的差值(2分钟)INSERT INTO `hp_report` VALUES (8, 'a', 'on', '2014-07-04 21:34:53');INSERT INTO `hp_report` VALUES (10, 'a', 'off', '2014-07-04 21:45:53');这个是11分钟。并把a只有所有的差值加起来。我要的结果是a 13b 16
可以把时间取出来以后再计算吗?
strtotime($row['ctime']);
echo (strtotime('2014-07-04 21:19:53')-strtotime('2014-07-04 21:17:53'));
结果是 120 单位秒
mysql> select * from hp_report;+----+------+---------+---------------------+| id | code | content | ctime |+----+------+---------+---------------------+| 2 | a | on | 2014-07-04 21:17:53 || 3 | a | abc | 2014-07-04 21:18:53 || 4 | a | off | 2014-07-04 21:19:53 || 5 | b | on | 2014-07-04 21:20:53 || 6 | b | abc | 2014-07-04 21:22:53 || 7 | b | off | 2014-07-04 21:29:53 || 8 | a | on | 2014-07-04 21:34:53 || 9 | a | abc | 2014-07-04 21:36:53 || 10 | a | off | 2014-07-04 21:45:53 || 11 | b | on | 2014-07-04 22:12:53 || 13 | b | abc | 2014-07-04 22:18:53 || 14 | b | off | 2014-07-04 22:19:53 |+----+------+---------+---------------------+12 rows in set (0.00 sec)mysql> select `code`,sum(k) from ( -> select `code`, -> TIMESTAMPDIFF(MINUTE,(select max(ctime) from hp_report where `code`=a.code and ctime<a.ctime),ctime) as k -> from hp_report a -> where content='off' -> ) t -> group by `code`;+------+--------+| code | sum(k) |+------+--------+| a | 10 || b | 8 |+------+--------+2 rows in set (0.00 sec)mysql>
select code, time_format( sum(timediff(btime, atime) ), '%i') as xtime from ( select code, ctime as atime, (select min(ctime) from hp_report where id>t.id and content='off') as btime from hp_report t where content='on' ) A group by code
code xtime a 13 b 16
感谢各位大神的回复。
非常感谢3楼xuzuning,结果是正确的。但是由于我的表中记录上百万条。所以运行起来较慢,大概2分多钟。不知道有没有办法提高下效率。
针对表设计的问题。由于on以后会有其他信息,不知道什么时候才会出现off。所以,如果放一条记录中,中间的信息就没办法存放了。
还有拆分表是不是指是让每个表的记录少些,还是有其他原因。请指教,谢谢了!!
还有能不能推荐一两本关于高效率sql语句的书,非常感谢!!