Mysql

選擇不同的-> 加入 3 個表

  • March 1, 2018

嗨,我需要混合(不僅僅是加入)三個表,但每個表上可能存在或不存在 id(請參見下面的範例)。

我花了幾天時間嘗試加入和聯合,但一直缺少一些數據!!!有人可以給我一個新的想法嗎?

謝謝

數據範例

如果 MySQL 已經實現FULL了連接,這將是相當簡單的:

select 
   id, 
   a.price as price_1701, 
   b.price as price_1702, 
   c.price as price_1703
from 
   t_1701 as a
   full join t_1702 as b using (id)
   full join t_1703 as c using (id)
order by
   id ;

不幸的是,它沒有(也沒有姐妹實現 MariaDB),所以你必須使用 aUNIONLEFT連接來恢復:

select 
   id, 
   a.price as price_1701, 
   b.price as price_1702, 
   c.price as price_1703
from 
   ( select id from t_1701
     union
     select id from t_1702
     union
     select id from t_1703
   ) as t
   left join t_1701 as a using (id)
   left join t_1702 as b using (id)
   left join t_1703 as c using (id)
order by
   id ;

您可以使用 union 來“混合”這些表,然後使用諸如 max 之類的聚合函式來過濾掉存在值的 null :

select id, max(price_1701), max(price_1702), max(price_1703)
from (
   select id, price as price_1701, null as price_1702, null as price_1703
   from t_1701
   union all
   select id, null as price_1701, price as price_1702, null as price_1703
   from t_1702
   union all
   select id, null as price_1701, null as price_1702, price as price_1703
   from t_1703
) as t
group by id

引用自:https://dba.stackexchange.com/questions/198609