MySQL join 语句只返回一条联合记录

Question

我有两个表:

要求:

+----+------+----+
| id | from | to |
+----+------+----+
|  1 | aaaa | aa |
+----+------+----+
|  2 | aaab | ab |
+----+------+----+
|  3 | aabb | bb |
+----+------+----+

请求状态

+----+--------+---------+---------+
| id | req_id |  status |  time   |
+----+--------+---------+---------+
|  1 |    1   | pending |  mm-dd  |
+----+--------+---------+---------+
|  2 |    1   | sent    |  mm-dd  |
+----+--------+---------+---------+
|  3 |    2   | pending |  mm-dd  |
+----+--------+---------+---------+
|  2 |    2   | failed  |  mm-dd  |
+----+--------+---------+---------+
|  4 |    3   | pending |  mm-dd  |
+----+--------+---------+---------+

在请求表上我必须记录:中间状态和完成请求状态。

我需要的是某种连接语句，每个请求只返回一个状态，但只有在失败或发送不存在时才返回挂起状态。

结果:

+----+------+----+---------+
| id | from | to | status  |
+----+------+----+---------+
|  1 | aaaa | aa | sent    |
+----+------+----+---------+
|  2 | aaab | ab | failed  |
+----+------+----+---------+
|  3 | aabb | bb | pending |
+----+------+----+---------+

select r.id, r.from, r.to, s.status  from requests r
join request_status s on r.id = s.req_status
where r.status not like 'pending'

将在没有待定状态的情况下返回。

Answer 1

看起来您正在尝试显示 request 的每一行连接到 request_status 中最近对应的行。

为了让它正常工作，您的 request_status.id 列需要是主键并且需要自动递增。也就是说，request_status 中的每个新行都必须具有更高的 id 值。这就是我们判断哪一行比另一行更新的方式。

然后，就很简单了。此子查询生成一组最新的 id 值。 ( http://sqlfiddle.com/#!9/c845b/1/0 )

        SELECT MAX(id) latest_id
          FROM request_status
         GROUP BY req_id

然后您在连接中使用子查询，如下所示 (http://sqlfiddle.com/#!9/c845b/3/0)

SELECT r.id, r.from, r.to, s.status
  FROM request r
  JOIN request_status s ON r.id = s.req_id
  JOIN (
            SELECT MAX(id) latest_id
              FROM request_status
             GROUP BY req_id
        ) t ON s.id = t.latest_id

第三个JOIN操作基本等同于

 WHERE s.id IN (
        SELECT MAX(id) latest_id
          FROM request_status
         GROUP BY req_id
       )

它为每个请求 ID 过滤掉除最新状态行之外的所有内容。