MySQL 通过 ID 将表一左连接到表二，其中表二行最新日期和 - mysql left join table one to table two by id where table two row newest date and

mysql left join table one to table two by id where table two row newest date and

我有两个表，表一个

+----+-------------------------------------------------+------------+
| id | pixel                                           | date       |
+----+-------------------------------------------------+------------+
|  2 | 7FB30~8BBEA~2B1FB~D5C54~205BD~05A28~9FAB6~F8D2A | 1417334510 |
|  3 | 7FB30~8BBEA~2B1FB~D5C54~205BD~05A28~9FAB6~F8D2A | 1417358993 |
|  4 | 7FB30~8BBEA~2B1FB~D5C54~205BD~05A28~9FAB6~F8D2A | 1417443262 |
+----+-------------------------------------------------+------------+

表二

+----+---------+--------+------------+
| id | lead_id | status | date       |
+----+---------+--------+------------+
| 11 |       3 |      2 | 1417359373 |
| 10 |       2 |      2 | 1417357705 |
| 12 |       2 |      4 | 1417422929 |
| 13 |       4 |      2 | 1417443292 |
+----+---------+--------+------------+

我想在 one.id 到two.lead_id和最新日期加入表一到表二然后在 2.status 等于 x 时选择它

所以结果看起来像这样

+--------+--------+-------------+------------+-----------+------------+
| one.id | two.id | two.lead_id | two.status | one.pixel | two.date   |
+--------+--------+-------------+------------+-----------+------------+
|   3    |    11  |     3       |       2    | 7FB30...  | 1417359373 |
|   2    |    12  |     2       |       4    | 7FB30...  | 1417422929 |
|   4    |    13  |     4       |       2    | 7FB30...  | 1417443292 |
+--------+--------+-------------+------------+-----------+------------+

谢谢。

我不确定"2.状态等于x"与问题或结果有什么关系。看起来您希望表 2 中的第一条记录位于表 1 上或之后。

这个想法是使用相关的子查询来获取表 2 中表 1 中每一行的行的 id。然后加入其余字段：

select t.id as one_id, t.two_id, t2.lead_id, t2.status, t.pixel, t2.date
from (select t1.*,
             (select t2.id
              from table2 t2
              where t2.date >= t1.date
              order by date
              limit 1
             ) as two_id
      from table1 t1
     ) t join
     table2 t2
     on t.two_id = t2.id;