使用一个MySQL查询获取报告

I have a MySQL Table 'logs' with the below data

我有一个带有以下数据的MySQL表'日志'

id  username    status   statusdatetime   mailid
-----------------------------------------------------------------------
1   abc         pull     3/21/2013 10:04  1753
2   abc         step1    3/21/2013 10:13  1753
3   abc         step2    3/21/2013 10:17  1753
4   abc         step1    3/21/2013 10:46  1753
5   abc         step2    3/21/2013 10:54  1753
6   abc         step3    3/21/2013 11:09  1753
7   abc         mailsent 3/21/2013 11:10  1753
8   abc         pull     3/21/2013 11:11  2113
9   abc         step1    3/21/2013 11:18  2113
10  abc         step1    3/21/2013 11:32  2113
11  abc         step2    3/21/2013 11:33  2113
12  abc         step3    3/21/2013 11:44  2113
13  abc         mailsent 3/21/2013 11:44  2113
14  def         pull     3/21/2013 10:21  120
15  def         step1    3/21/2013 10:22  120
16  def         step2    3/21/2013 10:36  120
17  def         step1    3/21/2013 10:37  120
18  def         step2    3/21/2013 10:38  120
19  def         step3    3/21/2013 10:39  120
20  def         mailsent 3/21/2013 10:39  120
21  def         pull     3/21/2013 10:40  1203
22  def         step1    3/21/2013 10:41  1203
23  def         step2    3/21/2013 10:50  1203
24  def         step3    3/21/2013 10:54  1203
25  def         mailsent 3/21/2013 10:55  1203

The help that i request is the below output:

我要求的帮助是以下输出:

id  username    mailid  Time (Seconds)
-----------------------------------------------------------------------
1   abc         1753    3977
2   abc         2113    1991
3   def         120     1101
4   def         1203    888

Explanation:

Each user and each mailid are grouped and the time difference between 'mailsent' and 'pull' from the column 'status' to be calculated!

每个用户和每个mailid都被分组,并且要计算列'status'中'mailsent'和'pull'之间的时差!

is this possible in one select query?

这可能在一个选择查询中?

2 个解决方案

#1

Try this query

试试这个查询

SELECT  
   a.username,
   a.mailid, 
   TIME_TO_SEC(TIMEDIFF(b.statusdatetime, a.statusdatetime)) AS DIFF
FROM 
   tbl a 
INNER JOIN
   tbl b 
ON 
   a.mailid = b.mailid AND 
   a.status = 'pull' AND
   b.status = 'mailsent'
GROUP BY 
   username, 
   mailid

FIDDLE

#2

If u have same sequence which goes from pull to mailsent status for each mailid, then use below query

如果你有相同的序列,从每个mailid的pull到mailsent状态,那么使用下面的查询

 select
     username,
     mailid,
     TIMESTAMPDIFF(SECOND,min(statusdatetime),max(statusdatetime)) as `Time`
 from logs
 group by mailid;

#1