MySQL 通过 ID 将表 1 左连接到表 2，其中表 2 ID 最新日期 - mysql left join table 1 to table 2 by id where table 2 id newest date

mysql left join table 1 to table 2 by id where table 2 id newest date

我有两个表，表一个

+----------+------------+------+-----+---------+----------------+
| Field    | Type       | Null | Key | Default | Extra          |
+----------+------------+------+-----+---------+----------------+
| id       | int(11)    | NO   | PRI | NULL    | auto_increment |
| pixel    | text       | NO   |     | NULL    |                |
| metadata | text       | NO   |     | NULL    |                |
| data     | text       | NO   |     | NULL    |                |
| attended | int(11)    | NO   |     | NULL    |                |
| alerted  | int(11)    | NO   |     | NULL    |                |
| date     | bigint(20) | NO   |     | NULL    |                |
+----------+------------+------+-----+---------+----------------+

表二

+------------------+------------+------+-----+---------+----------------+
| Field            | Type       | Null | Key | Default | Extra          |
+------------------+------------+------+-----+---------+----------------+
| id               | int(11)    | NO   | PRI | NULL    | auto_increment |
| lead_id          | int(11)    | NO   |     | NULL    |                |
| project_id       | int(11)    | NO   |     | NULL    |                |
| user_id          | int(11)    | NO   |     | NULL    |                |
| type             | int(11)    | NO   |     | NULL    |                |
| status           | int(11)    | NO   |     | NULL    |                |
| text             | text       | NO   |     | NULL    |                |
| comm             | int(11)    | NO   |     | NULL    |                |
| sum              | text       | NO   |     | NULL    |                |
| meeting_date     | text       | NO   |     | NULL    |                |
| meeting_location | text       | NO   |     | NULL    |                |
| date             | bigint(20) | NO   |     | NULL    |                |
+------------------+------------+------+-----+---------+----------------+

我想在 1.id = 2.lead_id 上连接表 1 到 2，其中 2.date 最大然后在 2.status 等于 x 时选择它

我试过了，关于一切（接受:D的答案），什么都没有得到。谢谢。这个问题是不同的它最后有一个 AND cluse

SELECT one.*, two.*
FROM one left join two on one.id=two.lead_id 
WHERE MAX(two.date) and two.status='x'

当您只想将单个值的最小值/最大值作为条件时，可以使用ORDER BY和LIMIT 0,1来获取单行:)

SELECT *
FROM table_one
INNER JOIN (
    SELECT *
    FROM table_two
    WHERE status = XXXXXXXXX
    ORDER BY date DESC
    LIMIT 0,1
) ON table_one.id = lead_id