我正在嘗試匹配 3 個表，每個表包含不同的值但具有相同的 ID，我希望得到如圖所示的混合結果（最終表）。我怎樣才能做到這一點？
uj5u.com熱心網友回復：
您可以從所有表中獲取所有產品而不重復，并將它們保存到臨時表中。然后簡單地LEFT JOIN使用三個表的臨時表。
DROP TEMPORARY TABLE IF EXISTS all_products;
CREATE TEMPORARY TABLE all_products
SELECT DISTINCT id, product FROM (
SELECT id, product FROM table1 UNION
SELECT id, product FROM table2 UNION
SELECT id, product FROM table3
) AS t;
ALTER TABLE all_products ADD INDEX id(id);
SELECT
t.id, t1.product,
t1.`count` AS `count table 1`,
t2.`count` AS `count table 2`,
t3.`count` AS `count table 3`
FROM
all_products AS t
LEFT JOIN table1 AS t1 ON t1.id=t.id
LEFT JOIN table2 AS t2 ON t2.id=t.id
LEFT JOIN table3 AS t3 ON t3.id=t.id
;
您還可以使用這樣的單個查詢：
SELECT
t.id, t.product,
t1.`count` AS `count table 1`,
t2.`count` AS `count table 2`,
t3.`count` AS `count table 3`
FROM (
SELECT DISTINCT id, product FROM (
SELECT id, product FROM table1 UNION
SELECT id, product FROM table2 UNION
SELECT id, product FROM table3
) AS a
) AS t
LEFT JOIN table1 AS t1 ON t1.id=t.id
LEFT JOIN table2 AS t2 ON t2.id=t.id
LEFT JOIN table3 AS t3 ON t3.id=t.id
;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/533852.html
標籤：数据库数据库加入联盟