MySQL加入最新行 [英] MySQL join on the latest row
问题描述
我有两个表agents
和calls
.座席将始终处于一个通话中.可能有另一个未决呼叫已分配给座席,而座席尚未应答.
I have two tables agents
and calls
. An agent will always be on one active call. There can be another pending call assigned to an agent that an agent has not yet answered.
我想编写一个查询,以测试按dateCreated
排序的最新呼叫是否与代理表中当前处于活动状态的呼叫相匹配.
I want to write a single query that tests to see if the latest call as sorted by dateCreated
matches the call that is currently active in the agent`s table.
这里是一个示例,其中当前通话与座席通话匹配: http://sqlfiddle. com/#!9/af5bd/5
Here is an example where the active call matches the agent's call: http://sqlfiddle.com/#!9/af5bd/5
下面是一个示例,其中正在进行的呼叫与座席的呼叫不匹配: http://sqlfiddle.com/#!9/70b7e0/8
Here is an example where the active call does not match the agent's call: http://sqlfiddle.com/#!9/70b7e0/8
我正在运行的查询是
# This does not work
SELECT count(*) from calls c
INNER JOIN agents a ON a.callId = c.id
ORDER BY c.dateCreated DESC LIMIT 1;
# This works
SELECT count(*) from calls c
INNER JOIN agents a ON a.callId = (SELECT id FROM calls ORDER BY dateCreated DESC LIMIT 1)
ORDER BY c.dateCreated DESC LIMIT 1;
第一个查询将始终返回1,但是第二个查询有效.我不是在查询中编写第二个查询的忠实拥护者.有办法解决吗?
The first query will always return 1, but the second query works. I'm not a big fan of writing a second query inside my query. Is there a way around this?
更新
也许这还不清楚,但是我基本上想看看calls
表中的最新条目是否与agents
表中的callId
相匹配.
Update
Maybe this was not clear, but I basically want to see if the latest entry in the calls
table matches the callId
from the agents
table.
推荐答案
使用返回最近呼叫ID的子查询加入agents
表:
Join the agents
table with a subquery that returns the most recent call ID:
SELECT COUNT(*)
FROM agents AS a
JOIN (SELECT id AS latest_call
FROM calls
ORDER BY dateCreated DESC
LIMIT 1) AS c ON a.callid = c.id
WHERE a.id = @agent_you_care_about
这篇关于MySQL加入最新行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!