mysql - 来自不同表的子查询最后日期/值

Question

我正在尝试将表 2 中的最后一个水果计数附加到表 1 的查询中。

table1                              table2                   
+--------+--------+-------+-----+   +--------+------+-------+
| fruit  | season | carbs | fat |   | fruit  | date | count |
+--------+------+---------+-----+   +--------+------+-------+
| apple  | summer |   21  |  0  |   | apple  | 2015 |  700  |
| banana | all yr |   27  |  0  |   | apple  | 2014 |  500  |
+--------+--------+-------+-----+   | banana | 2014 |  200  |
                                    | banana | 2013 |  300  |
                                    +--------+------+-------+

goal:
+--------+--------+-------+-----+------------+
| fruit  | season | carbs | fat | last_count |
+--------+------+---------+-----+------------+
| apple  | summer |   21  |  0  |     700    |
| banana | all yr |   27  |  0  |     200    |
+--------+--------+-------+-----+------------+

我能想到的就是这样的(不工作):

SELECT t1.*, t2.count AS last_count FROM table1 AS t1
INNER JOIN 
(SELECT fruit, count FROM table2 ORDER BY date DESC) t2 ON t1.fruit = t2.fruit

此外，我应该能够根据 table1 中的值过滤结果(例如 WHERE t1.carbs < 25 )

Answer 1

解决此问题的一种方法是将 table1 连接到 table2 两次。第一个连接是针对具有计数的完整 table2，第二个连接是针对每个水果的 table2 的子查询，其中仅包含最新水果的记录。

SELECT t1.*, t2.count AS last_count
FROM table1 t1
INNER JOIN table2 t2
    ON t1.fruit = t2.fruit
INNER JOIN
(
    SELECT fruit, MAX(date) AS date
    FROM table2
    GROUP BY fruit
) t3
    ON t2.fruit = t3.fruit AND
       t2.date  = t3.date