SQL数据分析之子查询的综合用法和案例题【耐心整理】

零、写在前面

本文所有代码均是在SQL ZOO平台进行，数据也该平台下的world表和一些其他平台提供的数据表，所有代码均已通过测试。

一、子查询基础用法

1、说明
子查询本身就是一段完整的查询语句，然后用括号英文括号(包裹嵌套在主查询语包虫,
子查询可以多层嵌套最常用的子查询运用在from和where子句中。

2、查询gdp高于欧洲每个国家的所有国家名，有一些国家gdp可能为null，需要进行排除

select name
from world
where gdp is not null 
and gdp>=(select max(gdp) from world where continent='Europe')
1
2
3
4

注意：记得子查询的select要加括号，而且返回的必须是一个值（max(gdp)），而不是一列值（gdp）

3、查询与澳大利亚Australia和智利Chile同一个大洲的国家和所属大洲

select continent,name
from world
where continent in (select continent from world where name in('Australia','Chile'))
order by continent,name
1
2
3
4

4、子查询详细解释
（1）子查询是可以自己正常独立运行的一段完整的查询语句,然后将子查询的查询结果作为主查询的一部分，因此子查询优先于主查询运行

（2）练习1是带比较运算符的子查询，要求子查询为标量子查询，即子查询结果为一行- -列(相当于-一个单元格)

（3）练习2是带in关键字的子查询，要求子查询为列子查询，即子查询结果为多行一列(单列)

（4）where子句中的子查询适用于查询条件无法一步到位， 需要先进行一步查询得到结果，基于这个查询结果再进行条件判断的情况，相当于我们无法直达时，需要进行换乘

二、子查询综合用法（难题）

1、查询2017年所有在爱丁堡的选区当选议员所在选区(constituency) 及其团队(party) ,已知爱丁堡选区编号为S14000021至S14000026，当选议员即各选区得票数最高的候选人

第一步：使用窗口函数对各个选区的候选人进行排序

select constituency,party,votes,
rank() over(PARTITION BY consitituency ORDER BY votes desc) as RV
from ge
where constituency between 'S14000021' and 'S14000026' and yr=2017
group by constituency,party,votes
1
2
3
4
5

第二步：将窗口函数作为from的子查询，筛选出当前选区得票数最高的候选人（议员）

select RK.constituency,RK.party
from (
select constituency,party,votes,
rank() over(PARTITION BY constituency ORDER BY votes desc) as RV
from ge
where constituency between 'S14000021' and 'S14000026' and yr=2017
group by constituency,party,votes
) as RK
where RK.RV=1
1
2
3
4
5
6
7
8
9

2、查询人口数（population）超过加拿大（Canada）但少于波兰（Poland）的国家，结果显示这些国家的国家名（name）和人口数（population）

select name,population
from world
where population > (
select population from world where name='Canada'
) and  population < (
select population from world where name='Poland'
)
1
2
3
4
5
6
7

3、查询所有国家人口均小于25000000的大洲，及其国家名（name）和人口数（population）

select continent,name,population
from world
where continent not in (
select distinct continent from world where population>25000000
)
order by continent;
1
2
3
4
5
6

重点：所有国家人口均小于，很难判断，但可以很容易把存在有国家大于该population的大洲找出来，再not in这些大洲

4、查询在欧洲（Europe）人均gdp大于英国（United Kingdom）的国家名

select name
from world
where continent='Europe'
and (gdp/population)>(
select (gdp/population) from world where name='United Kingdom'
)
1
2
3
4
5
6

5、查询每个大洲中最大的国家（判断area），显示该大洲（continent），国家名（name）和面积（area）

第一步：将每个大洲的国土面积进行排名

select continent,name,area,
rank() over(PARTITION BY continent ORDER BY area desc) as RA
from world
group by continent,name,area
1
2
3
4

第二步：把排名好的国家作为from子查询，再筛选出每个大洲排名第一的国家

select RK.continent,RK.name,RK.area
from (
select continent,name,area,
rank() over(PARTITION BY continent ORDER BY area desc) as RA from world
group by continent,name,area) as RK
where RK.RA=1
order by RK.continent;
1
2
3
4
5
6
7

6、查询法国和意大利每天新增治愈人数并从高到低排名，查询结果按国家名，截至日期（年月*日），新增治愈人数，按排名排序

第一步：查询法国和意大利的国家名，截止日期，每天新增治愈人数（后一天减去前一天）

select name
,date_format(whn,'%Y年%m月%d日') 截止日期
,(recovered-lag(recovered,1) over(PARTITION BY name ORDER BY whn)) 每天新增治愈人数
from covid
where name in ('France','Italy')
group by name,whn,recovered
1
2
3
4
5
6

第二步：将第一步查询结果作为from的子查询，筛选出name,截止日期,每天新增治愈人数，并按照每天新增治愈人数进行排名

select name
,截止日期
,每天新增治愈人数
,rank() over(PARTITION BY RE.name ORDER BY RE.每天新增治愈人数 desc) 排名
from (
select name
,date_format(whn,'%Y年%m月%d日') 截止日期
,(recovered-lag(recovered,1) over(PARTITION BY name ORDER BY whn)) 每天新增治愈人数
from covid
where name in ('France','Italy')
group by name,whn,recovered
) as RE
group by RE.name,RE.截止日期,RE.每天新增治愈人数
order by 排名
1
2
3
4
5
6
7
8
9
10
11
12
13
14

写在后面：文章总结归纳于自戴师兄的课程：https://www.bilibili.com/video/BV1ZM4y1u7uF?p=4
在此课程学习的基础上进行了一些修改和验证。