SQL statement that asks for two lines of time difference between different conditions in the same column

Table

CREATE TABLE ' Hp_report ' (' id ' int (ten) unsigned NOT NULL auto_increment, ' code ' varchar (255) ' is not null ' ' content ' mediumt Ext NOT NULL, ' CTime ' datetime isn't null, Engine=myisam DEFAULT Charset=utf8 auto_increment=662555; INSERT into ' Hp_repo RT ' 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 to ' 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 ' VALU ES (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 (' a ', ' off ', ' 2014-0 7-04 21:45:53 '); insert INTO ' hp_report ' values (one, ' 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 (' B ', ' off ', ' 2014-07-04 22:19:53 '); 

I want to find out the difference between a and b,content for the ctime of off, and on, and sum the difference
That

Insert INTO ' hp_report ' values (4,  ' A ', ' off ',  ' 2014-07-04 21:19:53 '), and insert INTO ' hp_report ' values (2,  ' A ', ' on ',  ' 2014-07-04 21:17:53 '); CTime difference (2 min) INSERT into ' hp_report ' values (8, '  a ', ' on ',  ' 2014-07-04 21:34:53 '); INSERT into ' Hp_report ' VALUES (  ' A ', ' off ',  ' 2014-07-04 21:45:53 '); this is 11 minutes. and add up all the difference in a. The result I want is a  13b   16

Ask the great gods to help. Thank you!!

Reply to discussion (solution)

Can we take the time out and calculate it later?
Strtotime ($row [' CTime ']);

Echo (Strtotime (' 2014-07-04 21:19:53 ')-strtotime (' 2014-07-04 21:17:53 '));
The result is 120 units of seconds

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-&Gt 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    b    

In fact, your storage scheme is very dangerous:
If a on or off is lost for some reason, the whole data relationship is confusing.
Therefore, if there is a correspondence between the lines, then a field should be added to clearly indicate the relationship
or split into two tables by relational database paradigm
For your data, you can also put on time and off time in one record

A good data structure can result in a multiplier effect.
On the contrary, it will be flawed.

Thank you for your great God's reply.
Thank you very much for the 3 floor xuzuning, the result is correct. But because my table records on millions. So it's slower to run, about 2 more minutes. I wonder if there is any way to improve efficiency.

Problems with table design. Since on will have additional information, do not know when the off will occur. So, if you put a record in, the middle of the information can not be stored.
There is also a split table that does not mean that there are fewer records for each table, or there are other reasons. Please advise, thank you!!

and can you recommend one or two books on high-efficiency SQL statements, thank you very much!!

