将多个表的行转换为列的MySQL查询

Question

我需要向您请教关于从3个表中进行MySQL select查询的问题。

我有3个类似的表：

table_1

menu_id | menu_name
      1 | 菜单1
      2 | 菜单2

table_2

item_id | menu_id | item_name
      1 |       1 | 项目1
      2 |       1 | 项目2
      3 |       2 | 项目3
      4 |       2 | 项目4

table_3

price_id | item_id | currency_code | price
       1 |       1 |           EUR | 3.65
       2 |       1 |           USD | 3.45
       3 |       2 |           EUR | 9.00
       4 |       4 |           LEV | 4.85
       5 |       4 |           RON | 9.60

我需要进行如下的输出SELECT：

item_id | item_name | menu_id | menu_name | p_EUR | p_USD | p_GBP | p_RON | p_LEV
      1 | 项目1    |       1 | 菜单1    | 3.65  | 3.45  | 0     | 0     | 0
      2 | 项目2    |       1 | 菜单1    | 9.00  | 0     | 0     | 0     | 0
      3 | 项目3    |       2 | 菜单2    | 0     | 0     | 0     | 0     | 0
      4 | 项目4    |       2 | 菜单2    | 0     | 0     | 0     | 9.60  | 4.85

我希望用table_3的行填充查询的列（p_EUR，p_USD等）。如果没有匹配的记录，返回0或NULL。 表通过table_1.menu_id=table_2.menu_id和table_2.item_id=table_3.item_id进行连接。

Answer 1

您可以使用case when

SELECT
t2.item_id,
t2.item_name,
t2.menu_id,
t1.menu_name,
 MAX(CASE WHEN t3.currency_code = 'EUR' THEN t3.price END) AS p_EUR,
 MAX(CASE WHEN t3.currency_code = 'USD' THEN t3.price END) AS p_USD,
 MAX(CASE WHEN t3.currency_code = 'GBP' THEN t3.price END) AS p_GBP,
 MAX(CASE WHEN t3.currency_code = 'RON' THEN t3.price END) AS p_RON,
 MAX(CASE WHEN t3.currency_code = 'LEV' THEN t3.price END) AS p_LEV
FROM table_2 AS t2
JOIN table_1 AS t1 ON t2.menu_id = t1.menu_id
LEFT JOIN table_3 AS t3 ON t2.item_id = t3.item_id
GROUP BY t2.item_id, t2.item_name, t2.menu_id, t1.menu_name;

如果您需要将null转换为0，可以使用COALESCE，如下所示 COALESCE(MAX(CASE WHEN t3.currency_code = 'LEV' THEN t3.price END), 0) AS p_LEV