SQL基于all语句的简化查询语句的写法

一份来自https://sqlzoo.net/wiki/SELECT_within_SELECT_Tutorial/zh 的sql子查询的题目

name国家名	continent洲份	area面积	population人口	gdp
Afghanistan	Asia	652230	25500100	20343000000
Albania	Europe	28748	2831741	12960000000
Algeria	Africa	2381741	37100000	188681000000
Andorra	Europe	468	78115	3712000000
Angola	Africa	1246700	20609294	100990000000
...

 

表列名含义：

name:国家名称

continent:该国家属于哪个洲

area:面积

population:人口

gdp:gdp

 

1.列出符合条件的国家名称，条件：国家认可大于俄罗斯(Russia)的人口

【知识点】标量子查询

select name 
from world 
where population > 
(select population 
from world 
where name = 'Russia');

2.列出欧洲每个国家的人均GDP，其中人均GDP要高于英国(United Kingdom)

【知识点】比较运算符（人均GDP=人口/gdp），逻辑运算符（and），标量子查询

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

3.在阿根廷(Argentina)和澳大利亞(Australia)所在的洲份中的国家有哪些？查找出国家名称和洲名称，并按国家名称排序

【知识点】在运算符in里使用子查询

select name, continent from world
where continent in
(select continent 
from world
where name='Argentina' or name='Australia')
order by name;

4.查找符合下面条件的国家名称和人口：国家的人口比加拿大(Canada)的多，但比波兰(Poland)的少

【知识点】在运算符between里使用标量子查询，这里用between查找出的范围边界值包括了边界值，所以要+1，和-1去掉边界值

比如范围是 1=<x<=10，（其中1是加拿大人口，10是波兰人口），为了不包括边界值，需要去掉两个边界值，变成 1+1=<x<= (10-1)

select name, population 
from world
where population between
(select population 
from world
where name='Canada')+1 and
(select population 
from world
where name='Poland')-1;

5.德国(Germany)在欧洲(Europe)國家的人口最多。奧地利(Austria)拥有德国总人口的11％。

查找欧洲的国家名称和每个国家的人口，其中人口以德国人口的百分比来显示人口数

【知识点】标量子查询，字符串连接函数concat,浮点数保留多少位round函数

select name, concat(round(population*100/(select population from world where name='Germany')), '%') AS population 
from world
where continent = 'Europe';

6.哪些国家的GDP比欧洲(Europe)的全部国家都要高呢?  (有些国家的记录中，GDP是空值NULL，没有填入资料）

【知识点】all的用法，子查询,条件中gdp>0用来去掉空值的情况

select name from world
where gdp > all
(select gdp from world
where continent = 'Europe' and gdp > 0);

7.在每一个州中找出最大面积的国家，查找出洲, 国家名字，面积。 (有些国家的记录中，面试是空值NULL，没有填入资料）

【知识点】all的用法，关联子查询（子查询查出洲内所有的面积，all 完成本洲内面积取最大）

select continent, name, area 
from world as x
where area >= all
(select area 
from world as y
where y.continent=x.continentand area>0);

8.列出洲份名称和国家名称，其中每个洲只取出一个国家（条件：该国家排序在这个洲的首位）

【知识点】all的用法，关联子查询（子查询查出洲内所有的国家名称，all 完成本洲内国家名称取最小）

select continent, name 
from world as x
where name <= all
(select name 
from world as y
where y.continent=x.continent);

9.找出符合条件的洲和国家名称，条件：该洲中的全部国家人口都有少于或等于 25000000 人口）

【知识点】all的用法，关联子查询（子查询查出洲内所有的人口，all语句完成人口筛选）

select name, continent, population 
from world as x
where 25000000 >= all
(select population 
from world as y
where y.continent=x.continent);

10.有些国家的人口是同洲份的所有其他国的3倍或以上。列出这些国家的名称和洲

【知识点】all的用法，关联子查询（子查询查出洲内人口的3倍数且国家不包含自身，all语句完成筛选）

select name, continent 
from world as x
where population > all
(select 3*population 
from world as y
where y.continent=x.continent and x.name <> y.name);