SELECT最早在DATETIME进行MySQL JOIN [英] MySQL JOIN by SELECT earliest DATETIME

问题描述

此查询将票证分配给谁，并计算票证关闭所需的平均四舍五入天数.

This query groups tickets by who they are assigned to and works out the average rounded number of days the ticket has taken to be closed.

SELECT a.id as theuser, round(avg(DATEDIFF( ta.dateClosed, t.dateAded ) * 1.0), 2) as avg
FROM tickets t join
     mdl_user a
     on find_in_set(a.id, t.assignedto) > 0
GROUP BY a.id ORDER BY avg ASC

我现在想加入故障单答案表以找出第一次响应的平均时间. 票证可能有多个答案，所以我只想得到第一个. 因此，我试图更改查询以使其无济于事.有人能说明我做错了什么吗?

I would now like to JOIN the ticketanswer table to find out the average time for first response. The ticket could have multiple answers so i just want to get the first one. Therefore I have tried to change the query to include this with no avail. Could anyone shed a light as to what im doing wrong?

SELECT a.id as theuser, round(avg(DATEDIFF( ta.dateAded , t.dateAded ) * 1.0), 2) as avg
FROM tickets t join
     mdl_user a
     on find_in_set(a.id, t.assignedto) > 0
INNER JOIN (SELECT MIN(ta.dateAded) as started FROM ticketanswer GROUP BY ta.ticketId) ta ON t.id = ta.ticketId
GROUP BY a.id ORDER BY avg ASC

推荐答案

对查询进行了一些小的修改.

Made some slight modifications to your query.

SELECT a.id as theuser, round(avg(DATEDIFF( ta.dateAded , t.dateAded ) * 1.0), 2) as avg
FROM tickets t join
 mdl_user a
 on find_in_set(a.id, t.assignedto) > 0
INNER JOIN (SELECT ticketid, MIN(dateAded) as started FROM ticketanswer GROUP BY ticketId) ta ON t.id = ta.ticketId
GROUP BY a.id ORDER BY avg ASC

这篇关于SELECT最早在DATETIME进行MySQL JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！