mysql数据过滤

ltskdhd1 于 2021-06-23 发布在 Mysql

关注(0)|答案(1)|浏览(589)

Id Conversationid    Agentid    Event       Timestamp
---------------------------------------------------------------------
1      1234          aaditya    ASSIGN      2018-08-02 09:19:50
2      1234          aaditya    REPLY       2018-08-02 09:24:46
3      1234          rehman     ASSIGN      2018-08-02 09:25:39
4      2345           pavan     ASSIGN      2018-08-03 11:24:35
5      2345           pavan     REPLY       2018-08-03 11:25:53
6      3456          sanjay     ASSIGN      2018-08-02 13:19:02
7      3456          sanjay     REPLY       2018-08-02 13:20:33
8      4567           rahul    ASSIGN       2018-08-05 04:49:54
9      4567           rahul    REPLY        2018-08-05 04:55:54
10     4567           rahul    ASSIGN       2018-08-05 04:49:54

我有一个如上所述的数据集。我要的id的地方，为一个会话id是分配给一个代理谁都分配和答复事件和差异b/w这两个是超过5分钟。i、从上面的数据集输出应该是id的1、2以及8和9的细节

mysql

来源：https://stackoverflow.com/questions/51763169/mysql-data-filteration

1条答案

按热度按时间

tp5buhyn1#

请使用mysql中的timestampdiff（）尝试下面的查询
注：id（1,2）之间的时间差小于5分钟，因此我们只能期望id（8,9）。

SELECT assigns.id AS AssignID, replies.id AS ReplyID, assigns.agentid AS Agentid
FROM
(SELECT id,agentid,TIMESTAMP FROM timeD WHERE EVENT = 'assign' GROUP BY TIMESTAMP) AS assigns
INNER JOIN
(SELECT id,agentid,TIMESTAMP FROM timeD WHERE EVENT = 'reply' GROUP BY TIMESTAMP) AS replies
ON assigns.agentid = replies.agentid 
AND TIMESTAMPDIFF(MINUTE,assigns.TIMESTAMP,replies.TIMESTAMP) > 5;

赞(0）回复(0）举报 2021-06-23

我来回答

mysql数据过滤

1条答案

相关问题

热门标签

最新问答