这是我的查询:
select calldate as call_start, DATE_ADD(calldate, Interval duration SECOND) as call_end, duration
from extensions
where ext = '2602' -- ext is the primary key
AND DATE_FORMAT(calldate, '%Y-%m-%d') = "2015-03-20"
order by calldate asc
返回以下内容:
我如何添加第四列以获取第二行的call_start与第一行的call_end之间的区别?像这样:
2015-03-20 10:21:20-2015-03-20 10:21:16 => 4秒,这4秒应作为第二行的第4个字段添加。
因此,对于所有调用,它应该看起来像:
call_start call_end duration difference
2015-03-20 10:19:41 2015-03-20 10:21:16 95 null
2015-03-20 10:21:20 2015-03-20 10:21:29 9 4
由于第一行从之前的调用开始没有call_end,因此应该为null。
解决方案:
SET @prev_end=null;
select calldate, date_add(calldate, interval duration SECOND) as end_date, duration,
TIME_FORMAT(SEC_TO_TIME(timestampdiff(second,@prev_end,calldate)), '%i:%s')as difference,
@prev_end:= date_add(calldate, interval duration SECOND) as test
from extensions
where ext = '2602'
AND DATE_FORMAT(calldate, '%Y-%m-%d') = "2015-03-20"
order by calldate asc;
输出:
考虑下表
mysql> select * from extensions;
+---------------------+----------+
| calldate | duration |
+---------------------+----------+
| 2015-03-20 10:19:41 | 95 |
| 2015-03-20 10:21:20 | 9 |
| 2015-03-20 10:21:35 | 277 |
| 2015-03-20 10:55:49 | 27 |
+---------------------+----------+
现在您可以得到区别
select
calldate as start_date,
end_date,
duration,
difference
from(
select
calldate,
duration,
date_add(calldate, interval duration SECOND) as end_date ,
timestampdiff(second,@prev_end,calldate) as difference,
@prev_end:= date_add(calldate, interval duration SECOND)
from extensions,(select @prev_end:=null)x
order by calldate
)x
+---------------------+---------------------+----------+------------+
| start_date | end_date | duration | difference |
+---------------------+---------------------+----------+------------+
| 2015-03-20 10:19:41 | 2015-03-20 10:21:16 | 95 | NULL |
| 2015-03-20 10:21:20 | 2015-03-20 10:21:29 | 9 | 4 |
| 2015-03-20 10:21:35 | 2015-03-20 10:26:12 | 277 | 6 |
| 2015-03-20 10:55:49 | 2015-03-20 10:56:16 | 27 | 1777 |
+---------------------+---------------------+----------+------------+
在上面的查询中,您需要在子查询中的where子句之前添加其他where子句。
where ext = '2602' -- ext is the primary key
AND DATE_FORMAT(calldate, '%Y-%m-%d') = "2015-03-20"
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句