SQL ZOO 练习题解法+解析【Select within Select】

【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