FROM 子查詢

SQL 資料操作

Mona Khalil

Data Scientist, Greenhouse Software

FROM 子查詢

重組並轉換你的資料
- 選取前先把長表轉為寬表
- 預先過濾資料
計算「彙總的彙總」
- 哪 3 支球隊主場進球平均最高？
  1. 計算各隊的 AVG
  2. 取出 AVG 值最高的 3 個

FROM 子查詢…

SELECT
  t.team_long_name AS team,
  AVG(m.home_goal) AS home_avg
FROM match AS m
LEFT JOIN team AS t
ON m.hometeam_id = t.team_api_id
WHERE season = '2011/2012'
GROUP BY team;

| team                 | home_avg         |
|----------------------|------------------|
| 1. FC Köln           | 1.13725490196078 |
| 1. FC Nürnberg       | 1.27058823529412 |
| 1. FSV Mainz 05      | 1.43697478991597 |
| AC Ajaccio           | 1.12280701754386 |

…到主查詢！


FROM (SELECT
          t.team_long_name AS team,
          AVG(m.home_goal) AS home_avg
      FROM match AS m
      LEFT JOIN team AS t
      ON m.hometeam_id = t.team_api_id
      WHERE season = '2011/2012'
      GROUP BY team)

…到主查詢！


FROM (SELECT
          t.team_long_name AS team,
          AVG(m.home_goal) AS home_avg
      FROM match AS m
      LEFT JOIN team AS t
      ON m.hometeam_id = t.team_api_id
      WHERE season = '2011/2012'
      GROUP BY team) AS subquery

…到主查詢！

SELECT team, home_avg
FROM (SELECT
          t.team_long_name AS team,
          AVG(m.home_goal) AS home_avg
      FROM match AS m
      LEFT JOIN team AS t
      ON m.hometeam_id = t.team_api_id
      WHERE season = '2011/2012'
      GROUP BY team) AS subquery

…到主查詢！

SELECT team, home_avg
FROM (SELECT
          t.team_long_name AS team,
          AVG(m.home_goal) AS home_avg
      FROM match AS m
      LEFT JOIN team AS t
      ON m.hometeam_id = t.team_api_id
      WHERE season = '2011/2012'
      GROUP BY team) AS subquery
ORDER BY home_avg DESC
LIMIT 3;

| team           | home_avg |
|----------------|----------|
| FC Barcelona   | 3.8421   |
| Real Madrid CF | 3.6842   |
| PSV            | 3.3529   |

重點整理

你可以在一個 FROM 中建立多個子查詢
- 要加別名！
- 也可互相連接（JOIN）！
你可以在 FROM 把子查詢與表格做 JOIN
- 兩邊都要有用來連接的欄位！

一起來練習吧！

SQL 資料操作