[MYSQL] JOIN 加上 子查詢

表1 客戶主檔 user
uid | name
1 | AAA
2 | BBB
3 | CCC

表2 購買記錄 buy
uid | money
1 | 100
1 | 200
2 | 100

表3 聯絡方式 connect
uid | tel | city
1 | 0911111111 | taipei
1 | 0922222222 | taichung

如果只要撈出每個客戶的總購買金額，很簡單，語法如下：
SELECT
  user.name, SUM(buy.money)
FROM
  user
  LEFT JOIN buy ON user.uid = buy.uid
GROUP BY
  user.uid

問題來了，當我們需要撈出每個客戶的總購買金額，同時必須帶出聯絡方式（一筆就好），常見的錯誤語法如下：
SELECT
  user.name, SUM(buy.money), connect.tel, connect.city
FROM
  user
  LEFT JOIN buy ON user.uid = buy.uid
  LEFT JOIN connect ON user.uid = connect.uid
GROUP BY
  user.uid

因為當 JOIN 表3後，資料相乘下筆數會變多，SUM之後的金額就會變大，所以，需要透過子查詢，來達成我們的目的，語法如下：
SELECT
  user.name, SUM(buy.money), connect.tel, connect.city
FROM
  user
  LEFT JOIN buy ON user.uid = buy.uid
  LEFT JOIN (SELECT uid, tel, city FROM connect GROUP BY uid) AS connect ON user.uid = connect.uid
GROUP BY
  user.uid

備註：子查詢語法中 SELECT 的項目很重要，如果外層需要使用到時，都必須要子查詢中先SELECT出來。