<p>我需要向您請教從3個表格中進行MySQL select查詢的問題。 </p> <p>我有3張類似的表：</p> <p><strong>table_1</strong></p> <pre class="brush:php;toolbar:false;">menu_id | menu_name 1 | 菜單1 2 | 選單2</pre> <p><strong>table_2</strong></p> <pre class="brush:php;toolbar:false;">item_id | menu_id | item_name 1 | 1 | 項目1 2 | 1 | 項目2 3 | 2 | 項目3 4 | 2 | 項目4</pre> <p><strong>table_3</strong></p> <pre class="brush:php;toolbar:false;">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</pre> <p>我需要進行如下的輸出SELECT：</p> <pre class="brush:php;toolbar:false;">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</pre> <p>我希望用table_3的行填入查詢的列（p_EUR，p_USD等）。如果沒有符合的記錄，則回傳0或NULL。 表格透過<code>table_1.menu_id=table_2.menu_id</code>和<code>table_2.item_id=table_3.item_id</code>連接。 </p>

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;