【Select within Select】部分题目及解析
表结构如下：
#List the name and continent of countries in the continents containing either Argentina or Australia. Order by name of the country.
```
SELECT name, continent FROM world
WHERE continent IN
(SELECT continent FROM world
WHERE name="Argentina" OR
name="Australia")
ORDER BY name
```
#Germany (population 80 million) has the largest population of the countries in Europe. Austria (population 8.5 million) has 11% of the population of Germany.Show the name and the population of each country in Europe. Show the population as a percentage of the population of Germany.
这题涉及concat 函数的用法，此函数用于连接字符。
1、功能：将多个字符串连接成一个字符串。
2、语法：concat(str1, str2,...)
返回结果为连接参数产生的字符串，如果有任何一个参数为null，则返回值为null。
Round（）函数 这里要注意，（population,-3)表示population精确到千位
SELECT ROUND(column_name,decimals) FROM table_name
SELECT name,
CONCAT(
ROUND(population/
(SELECT population FROM world WHERE name= 'Germany')*100,0),'%')
FROM world
WHERE continent='Europe'
#Which countries have a GDP greater than every country in Europe? [Give the name only.] (Some countries may have NULL gdp values)这里涉及all函数的用法，以及 is not null 的表述。
SELECT name FROM world
WHERE gdp>All(SELECT gdp FROM world WHERE continent='Europe'
AND gdp IS NOT NULL)
#Find the largest country (by area) in each continent, show the continent, the name and the area:(这里涉及相关子查询 correlated subquery）
SELECT continent,name,area FROM world x
WHERE area>= ALL(SELECT area FROM world y
WHERE x.continent=y.continent AND area>0)
#List each continent and the name of the country that comes first alphabetically.
【Solution1】All 可以对name 进行“排序并取第一项的值”
SELECT continent,name FROM world x
WHERE name<=ALL
(SELECT name FROM world y WHERE x.continent=y.continent)
【Solution2】用OVER 函数（可以类推问题，求考试成绩排名前三的学生）
开窗函数over的常用方法：（Mysql里用不了over）
1.为每条数据显示聚合信息.(聚合函数() over())
2.为每条数据提供分组的聚合函数结果(聚合函数() over(partition by 字段) as 别名)
3.与排名函数一起使用(row number() over(order by 字段) as 别名)
如何使用：https://www.cnblogs.com/helianthus33/p/10169182.html
与group by的区别
https://blog.csdn.net/dwt1415403329/article/details/87835383
group by 和 partition by 都有分组统计的功能，但是partition by并不具有group by的汇总功能。partition by统计的每一条记录都存在，而group by将所有的记录汇总成一条记录(类似于distinct EmpDepartment 去重)。partition by可以和聚合函数结合使用，同时具有其他高级功能。
SELECT continent,name FROM
(SELECT continent,name,row_number()
OVER(PARTITION BY continent ORDER BY name)
#相当于根据continent对name进行分类，然后根据name 进行排序，据此得到row_number(表中命名为num)
AS num FROM world ) R
WHERE R.num=1
#Find the continents where all countries have a population <= 25000000. Then find the names of the countries associated with these continents. Show name, continent and population.
【错解】
SELECT name,continent,population FROM world x
WHERE 25000000>=
MAX(SELECT population FROM world y
WHERE x.continent=y.continent)
【Solution1】注意max的位置
SELECT name,continent,population FROM world x
WHERE 25000000>=
(SELECT MAX(population) FROM world y
WHERE x.continent=y.continent)
【Solution2】把错解里的max换成ALL就行了
SELECT name,continent,population FROM world x
WHERE 25000000>=
ALL(SELECT population FROM world y
WHERE x.continent=y.continent)
#Some countries have populations more than three times that of any of their neighbours (in the same continent). Give the countries and continents.
SELECT name,continent FROM world x
WHERE x.population >=
ALL(SELECT population*3 FROM world y #population*3的位置要注意放到括号里面
WHERE x.continent=y.continent
AND x.name!=y.name #要注意这里，不能自己和自己比
AND y.population>0)
全部练习地址：https://zh.sqlzoo.net/wiki/SELECT_within_SELECT_Tutorial
