求同一列不同條件形成的兩行的時間差的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');

我想求出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語句的書，非常感謝！！

