mysql两条记录同一列的差值计算

Question

mysql

Answer 1

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 2

SELECT a.`code`,SEC_TO_TIME(SUM(TIME_TO_SEC(ss))) FROM (
       SELECT a.`code`,a.`ctime`,MIN(a1.`ctime`),TIMEDIFF(MIN(a1.`ctime`),a.`ctime`) AS ss FROM `hp_report` A LEFT JOIN `hp_report` A1 
       ON a1.`code`=a.`code` AND a1.`ctime`>a.`ctime`
       
       WHERE A.`content`='on' AND A1.`content`='off' GROUP BY a.`code`,a.`ctime`) a GROUP BY a.`code`

Answer 3

谢谢两位大神的回复。不过1楼的好像结果不对，还是谢谢了。
谢谢WWWWA你的sql语句对了。但是由于我的表中有百万条记录，所以比较慢，能不能优化下。非常感谢！！！

Answer 4

//只适合每条on的数据都有对应的off情况使用
set @on := 0,@off := 0;
select m.`code`,sum(m.mtime)mtime from
(
select a.`code`,((UNIX_TIMESTAMP(b.ctime) - UNIX_TIMESTAMP(a.ctime)) / 60)as mtime from 
(
select (@on := @on + 1)as `on`,id,`code`,ctime from hp_report where content = 'on' order by ctime
)a
left join
(
select (@off := @off + 1)as `off`,id,`code`,ctime from hp_report where content = 'off' order by ctime
)b
on a.code = b.code and a.`on` = b.`off`
)m
group by m.`code`;

Answer 5

谢谢WWWWA你的sql语句对了。但是由于我的表中有百万条记录，所以比较慢，能不能优化下。非常感谢！！！
在CODE、content、ctime上建立索引