我有两个表,表一个
+----+-------------------------------------------------+------------+
| 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;