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屋！