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

familyX 2014-07-04 10:43:26

表



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  13

b   16

求大神们帮帮忙。谢谢了！！

...全文

1611 5 打赏收藏转发到动态举报

写回复

用AI写文章

5 条回复

切换为时间正序

请发表友善的回复…

发表回复

WWWWA 2014-07-06

打赏
举报

引用 3 楼 familyX 的回复:

谢谢两位大神的回复。不过1楼的好像结果不对，还是谢谢了。 [quote=引用 2 楼 WWWWA 的回复:] 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`

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

ayzen1988 2014-07-05

打赏
举报



//只适合每条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`;

familyX 2014-07-05

打赏
举报

谢谢两位大神的回复。不过1楼的好像结果不对，还是谢谢了。

引用 2 楼 WWWWA 的回复:

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`

谢谢WWWWA你的sql语句对了。但是由于我的表中有百万条记录，所以比较慢，能不能优化下。非常感谢！！！

WWWWA 2014-07-05

打赏
举报

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`

ACMAIN_CHM 2014-07-05

打赏
举报

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>