嵌套子查询
SQL 中的数据处理
Mona Khalil
Data Scientist, Greenhouse Software
什么是嵌套子查询？
- 子查询嵌套在另一子查询中
- 可执行多层转换
一个子查询…
- 各国平均值与总体平均值相差多少？
SELECT
c.name AS country,
AVG(m.home_goal + m.away_goal) AS avg_goals,
AVG(m.home_goal + m.away_goal) -
(SELECT AVG(home_goal + away_goal)
FROM match) AS avg_diff
FROM country AS c
LEFT JOIN match AS m
ON c.id = m.country_id
GROUP BY country;
一个子查询…
| country | avg_goals | avg_diff |
|-------------|-----------|----------|
| Belgium | 2.8015 | 0.096 |
| England | 2.7105 | 0.005 |
| France | 2.4431 | -0.2624 |
| Germany | 2.9016 | 0.196 |
| Italy | 2.6168 | -0.0887 |
| Netherlands | 3.0809 | 0.3754 |
| Poland | 2.425 | -0.2805 |
| Portugal | 2.5346 | -0.1709 |
| Scotland | 2.6338 | -0.0718 |
| Spain | 2.7671 | 0.0616 |
| Switzerland | 2.9297 | 0.2241 |
…在子查询里！
- 各月总进球与"月度总进球平均值"相比差多少？
SELECT
EXTRACT(MONTH FROM date) AS month,
SUM(m.home_goal + m.away_goal) AS total_goals,
SUM(m.home_goal + m.away_goal) -
(SELECT AVG(goals)
FROM (SELECT
EXTRACT(MONTH FROM date) AS month,
SUM(home_goal + away_goal) AS goals
FROM match
GROUP BY month)) AS avg_diff
FROM match AS m
GROUP BY month;
内层子查询
SELECT
EXTRACT(MONTH from date) AS month,
SUM(home_goal + away_goal) AS goals
FROM match
GROUP BY month;
| month | goals |
|-------|-------|
| 01 | 2988 |
| 02 | 3768 |
| 03 | 3936 |
| 04 | 4055 |
| 05 | 2719 |
| 06 | 84 |
| 07 | 366 |
外层子查询
SELECT AVG(goals)
FROM (SELECT
EXTRACT(MONTH from date) AS month,
SUM(home_goal + away_goal) AS goals
FROM match
GROUP BY month)
2944.75
最终查询
SELECT
EXTRACT(MONTH FROM date) AS month,
SUM(m.home_goal + m.away_goal) AS total_goals,
SUM(m.home_goal + m.away_goal) -
(SELECT AVG(goals)
FROM (SELECT
EXTRACT(MONTH FROM date) AS month,
SUM(home_goal + away_goal) AS goals
FROM match
GROUP BY month) AS s) AS diff
FROM match AS m
GROUP BY month;
| month | total_goals | diff |
|-------|-------------|----------|
| 01 | 5821 | -36.25 |
| 02 | 7448 | 1590.75 |
| 03 | 7298 | 1440.75 |
| 04 | 8145 | 2287.75 |
相关的嵌套子查询
- 嵌套子查询可为相关或非相关
- 或两者结合
- 可引用"外层子查询"或"主查询"的信息
相关的嵌套子查询
- 2011/2012 赛季，各国场均进球是多少？
SELECT
c.name AS country,
(SELECT AVG(home_goal + away_goal)
FROM match AS m
WHERE m.country_id = c.id
AND id IN (
SELECT id
FROM match
WHERE season = '2011/2012')) AS avg_goals
FROM country AS c
GROUP BY country;
相关的嵌套子查询
- 2011/2012 赛季，各国场均进球是多少？
SELECT
c.name AS country,
(SELECT AVG(home_goal + away_goal)
FROM match AS m
WHERE m.country_id = c.id
AND id IN (
SELECT id -- 开始内层子查询
FROM match
WHERE season = '2011/2012')) AS avg_goals
FROM country AS c
GROUP BY country;
相关的嵌套子查询
- 2011/2012 赛季，各国场均进球是多少？
SELECT
c.name AS country,
(SELECT AVG(home_goal + away_goal)
FROM match AS m
WHERE m.country_id = c.id -- 与主查询相关
AND id IN (
SELECT id -- 开始内层子查询
FROM match
WHERE season = '2011/2012')) AS avg_goals
FROM country AS c
GROUP BY country;
相关的嵌套子查询
| country | avg_goals |
|-------------|------------------|
| Belgium | 2.87916666666667 |
| England | 2.80526315789474 |
| France | 2.51578947368421 |
| Germany | 2.85947712418301 |
| Italy | 2.58379888268156 |
| Netherlands | 3.25816993464052 |
| Poland | 2.19583333333333 |
| Portugal | 2.64166666666667 |
| Scotland | 2.6359649122807 |
| Spain | 2.76315789473684 |
| Switzerland | 2.62345679012346 |
Passons à la pratique !
SQL 中的数据处理
