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 中可创建多个子查询
- 记得起别名！
- 可以连接它们！
也可在 FROM 中把子查询与表连接
- 确保两侧都有连接列！

让我们来练习！

SQL 中的数据处理