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

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

文章目录

​​零、写在前面​​​​一、子查询基础用法​​​​二、子查询综合用法（难题）​​

零、写在前面

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

一、子查询基础用法

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

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

select namefrom worldwhere gdp is not null and gdp>=(select max(gdp) from world where continent='Europe')

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

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

select continent,namefrom worldwhere continent in (select continent from world where name in('Australia','Chile'))order by continent,name

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 RVfrom gewhere constituency between 'S14000021' and 'S14000026' and yr=2017group by constituency,party,votes

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

select RK.constituency,RK.partyfrom (select constituency,party,votes,rank() over(PARTITION BY constituency ORDER BY votes desc) as RVfrom gewhere constituency between 'S14000021' and 'S14000026' and yr=2017group by constituency,party,votes) as RKwhere RK.RV=1

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

select name,populationfrom worldwhere population > (select population from world where name='Canada') and population < (select population from world where name='Poland')

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

select continent,name,populationfrom worldwhere continent not in (select distinct continent from world where population>25000000)order by continent;

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

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

select namefrom worldwhere continent='Europe'and (gdp/population)>(select (gdp/population) from world where name='United Kingdom')

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

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

select continent,name,area,rank() over(PARTITION BY continent ORDER BY area desc) as RAfrom worldgroup by continent,name,area

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

select RK.continent,RK.name,RK.areafrom (select continent,name,area,rank() over(PARTITION BY continent ORDER BY area desc) as RA from worldgroup by continent,name,area) as RKwhere RK.RA=1order by RK.continent;

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

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

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

第二步：将第一步查询结果作为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 covidwhere name in ('France','Italy')group by name,whn,recovered) as REgroup by RE.name,RE.截止日期,RE.每天新增治愈人数order by

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。