欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  后端开发

求同一列不同条件形成的两行的时间差的sql语句

程序员文章站 2022-05-31 15:58:14
...
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');


我想求出a和b,content 为off,和on 之间的ctime的差值,并把差值求和
即:
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 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 


其实你的这个存储方案是很危险的:
如果由于某种原因丢失了一个 on 或 off ,那么整个数据关系就混乱了
所以若行间存在对应关系的话,则应增加一个字段来明确指示这种关系
或者按关系型数据库范式拆分成两个表
对于你的数据,更可以将 on time 和 off time 放在一条记录中

一个好的数据结构可以带来事半功倍的效果
反之事倍功半不说,还会漏洞百出

感谢各位大神的回复。
非常感谢3楼xuzuning,结果是正确的。但是由于我的表中记录上百万条。所以运行起来较慢,大概2分多钟。不知道有没有办法提高下效率。

针对表设计的问题。由于on以后会有其他信息,不知道什么时候才会出现off。所以,如果放一条记录中,中间的信息就没办法存放了。
还有拆分表是不是指是让每个表的记录少些,还是有其他原因。请指教,谢谢了!!

还有能不能推荐一两本关于高效率sql语句的书,非常感谢!!