同じ列内の異なる条件によって形成された 2 つの行間の時間差を見つける 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、コンテンツがオフ、オンの 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<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>

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 

実際、ストレージ ソリューションは非常に危険です:
何らかの理由でオンまたはオフが失われると、データ関係全体が混乱します
したがって、行が対応する関係がある場合は、この関係を明確に示すフィールドを追加する必要があります
または、リレーショナル データベースのパラダイムに従って 2 つのテーブルに分割する必要があります
データについては、1 つのレコードにオンタイムとオフタイムを含めることもできます

A良いデータ構造は、半分の労力で約2倍の結果をもたらすことができます
逆に、半分の労力で2倍の結果は抜け穴だらけになります

皆さん、ご返信ありがとうございます。

3階のxuzuningさん、本当にありがとうございます、結果は正解でした。しかし、テーブルには何百万ものレコードがあるためです。そのため、実行速度が遅くなり、約 2 分以上かかります。効率を上げる方法があるかどうかはわかりません。

テーブルのデザインに関する質問。 on以降は別の情報もあるのでoffはいつ出るかわかりません。したがって、レコードに置いた場合、途中の情報を保存することはできません。
また、テーブルを分割すると、各テーブルのレコードが少なくなるということですか、それとも他の理由がありますか?アドバイスをお願いします、ありがとうございます！ ！

効率的な SQL ステートメントに関する本を 1 冊か 2 冊お勧めできますか? よろしくお願いします。 ！