This tutorial looks at how we can use SELECT statements within SELECT statements to perform more complex queries.
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 |
... |
8.List each continent and the name of the country that comes first alphabetically.
*******方法一*******
select continent,min(name)
from world
group by continent
order by continent
*******方法二*******
select continent,name from world
group by continent)
(1)为什么group by可以这么用?
*******方法三*******
select continent,name from world x where x.name=(select y.name from world y where y.continent=x.continent order by name limit 1)
continent | name |
---|---|
Africa | Algeria |
Asia | Afghanistan |
Caribbean | Antigua and Barbuda |
Eurasia | Armenia |
Europe | Albania |
North America | Belize |
Oceania | Australia |
South America | Argentina |
(1)为什么用where y.continent=x.contient,如果不用
select continent,name from world x where x.name=(select y.name from world y order by name limit 1)
执行结果是:
continent | name |
---|---|
Asia | Afghanistan |
(2)=如果换成in,执行错误
9.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
where continent in(select continent from world
group by continent
having max(population)<=25000000)
*******方法二*******
SELECT name, continent, population FROM world x
WHERE 25000000>=ALL (SELECT population FROM world y
WHERE x.continent=y.continent
AND population>0)
10.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/3> all(select population from world y
where x.continent=y.continent and x.name!=y.name and y.population>0)