MySQL JOIN by SELECT 最早的 DATETIME

Question

此查询按分配给谁的工单分组，并计算出工单关闭所需的平均四舍五入天数。

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

我现在想加入 ticketsanswer 表以找出第一次响应的平均时间。这张票可能有多个答案，所以我只想得到第一个。因此，我试图更改查询以包含此查询但无济于事。谁能说明我做错了什么？

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

Answer 1

对您的查询做了一些细微的修改。

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