【Select within Select】部分題目及解析
表結(jié)構(gòu)如下：
#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 函數(shù)的用法，此函數(shù)用于連接字符。
1、功能：將多個字符串連接成一個字符串。
2、語法：concat(str1, str2,...)
返回結(jié)果為連接參數(shù)產(chǎn)生的字符串，如果有任何一個參數(shù)為null，則返回值為null。
Round（）函數(shù) 這里要注意，（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函數(shù)的用法，以及 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:(這里涉及相關(guān)子查詢 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 進(jìn)行“排序并取第一項的值”
SELECT continent,name FROM world x
WHERE name<=ALL
? (SELECT name FROM world y WHERE x.continent=y.continent)
【Solution2】用OVER 函數(shù)（可以類推問題，求考試成績排名前三的學(xué)生）
開窗函數(shù)over的常用方法：（Mysql里用不了over）
1.為每條數(shù)據(jù)顯示聚合信息.(聚合函數(shù)() over())
2.為每條數(shù)據(jù)提供分組的聚合函數(shù)結(jié)果(聚合函數(shù)() over(partition by 字段) as 別名)
3.與排名函數(shù)一起使用(row number() over(order by 字段) as 別名)
如何使用：https://www.cnblogs.com/helianthus33/p/10169182.html
與group by的區(qū)別
https://blog.csdn.net/dwt1415403329/article/details/87835383
group by 和 partition by 都有分組統(tǒng)計的功能，但是partition by并不具有g(shù)roup by的匯總功能。partition by統(tǒng)計的每一條記錄都存在，而group by將所有的記錄匯總成一條記錄(類似于distinct EmpDepartment 去重)。partition by可以和聚合函數(shù)結(jié)合使用，同時具有其他高級功能。
SELECT continent,name FROM
? ? (SELECT continent,name,row_number()
? ? ? ?OVER(PARTITION BY continent ORDER BY name)
#相當(dāng)于根據(jù)continent對name進(jìn)行分類，然后根據(jù)name 進(jìn)行排序，據(jù)此得到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)?
全部練習(xí)地址：https://zh.sqlzoo.net/wiki/SELECT_within_SELECT_Tutorial
