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

Question

mysql，php

Answer 1

可以把时间取出来以后再计算吗？
strtotime($row['ctime']); echo (strtotime('2014-07-04 21:19:53')-strtotime('2014-07-04 21:17:53')); 
结果是 120 单位秒

Answer 2

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>

Answer 3

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 放在一条记录中一个好的数据结构可以带来事半功倍的效果
反之事倍功半不说，还会漏洞百出

Answer 4

感谢各位大神的回复。
非常感谢3楼xuzuning，结果是正确的。但是由于我的表中记录上百万条。所以运行起来较慢，大概2分多钟。不知道有没有办法提高下效率。针对表设计的问题。由于on以后会有其他信息，不知道什么时候才会出现off。所以，如果放一条记录中，中间的信息就没办法存放了。
还有拆分表是不是指是让每个表的记录少些，还是有其他原因。请指教，谢谢了！！还有能不能推荐一两本关于高效率sql语句的书，非常感谢！！