mysql - MySQL在行中合并SELECT结果

假设我有2个相同的表格，如下所示：

table1              table2
+--------+------+   +--------+------+
| fruit  | year |   | fruit  | year |
+--------+------+   +--------+------+
| apple  | 2015 |   | apple  | 2014 |
| banana | 2014 |   | apple  | 2013 |
| banana | 2013 |   | banana | 2011 |
+--------+------+   +--------+------+

我想像这样在每张桌子上得到水果的最后一年：

+--------+---------+---------+
| fruit  | t1.last | t2.last |
+--------+---------+---------+
| apple  |   2015  |   2014  |
| banana |   2014  |   2011  |
+--------+---------+---------+

对于单个表，可以使用简单的SELECT语句：

SELECT fruit, max( year ) AS last FROM table1 GROUP BY fruit
result:
+--------+------+
| fruit  | last |
+--------+------+
| apple  | 2015 |
| banana | 2014 |
+--------+------+

我不知道如何将第二个表中每个水果的“去年”作为相邻行“追加”。

最佳答案

只需JOIN这两个表。

SELECT fruit,
    max(t1.year) AS `t1.last`,
    max(t2.year) AS `t2.last`
FROM table1 AS t1
JOIN table2 AS t2 USING(fruit)
GROUP BY fruit

演示：http://www.sqlfiddle.com/#!2/f3b71/1

关于mysql - MySQL在行中合并SELECT结果，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/28092578/