从左边连接 mysql 获取两列中的两行 - Get two rows in two columns from left join mysql

Get two rows in two columns from left join mysql

我有两个表格，下面给出了结构和数据。

table1
id, name
1, abc
2, xyz

table2
id, table1_id, type, other_name
1, 1, 1, hello
2, 1, 2, world
3, 2, 1, wonder
4, 2, 2, this world

需要包含如下列的行的结果：

table1.id, table1,name, table2.other_name where table2.type=1, table2.other_name where table2.type=2

我试过：

SELECT
table1.id, table1,name,
IF(table2.type=1, table2.other_name,NULL) AS current_name,
IF(table2.type=2, table2.other_name,NULL) AS previous_name
FROM table1
LEFT JOIN table2 ON table2.table1_id = table1.id
GROUP BY table1.id;

但它返回如下：

1, abc, hello, NULL
2, xyz, wonder, NULL

而我希望得到这样的结果：

1, abc, hello, world
2, xyz, wonder, this world

请帮助伙计们！

您应该使用聚合函数，这些函数使用带有"分组依据"的多行，也可以将 table1.name 移动到"分组依据"下。

SELECT table1.id, 
       table1.name,
       max(IF(table2.type=1, table2.other_name, NULL)) AS current_name,
       max(IF(table2.type=2, table2.other_name, NULL)) AS previous_name
FROM table1
LEFT JOIN table2 ON table2.table1_id = table1.id
GROUP BY table1.id, table1.name;