Difference between revisions of "SELECT within SELECT Tutorial/zh"

From SQLZOO
Jump to navigation Jump to search
Line 18: Line 18:
  
 
<div class="progress_panel"><div>
 
<div class="progress_panel"><div>
   <div class="summary">Summary</div>
+
   <div class="summary"></div>
 
   <div class="progressbarbg">
 
   <div class="progressbarbg">
 
     <div class="progressbar"></div>
 
     <div class="progressbar"></div>
Line 48: Line 48:
  
 
<div class='qu'>
 
<div class='qu'>
<p class='imper'>Show the countries in Europe with a per capita GDP greater than 'United Kingdom'.</p>
+
<p class='imper'>列出歐州每國家的人均GDP,當中人均GDP要高於英國'United Kingdom'的數值。</p>
<div class='hint' title='Per Capita GDP'>The per capita GDP is the gdp/population</div>
+
<div class='hint' title='人均GDP'>人均GDP即是 gdp除以population</div>
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
  
Line 64: Line 64:
 
<div class='qu'>
 
<div class='qu'>
 
<span class='param respectorder'></span>
 
<span class='param respectorder'></span>
<p class='imper'>List the '''name''' and '''continent''' of countries in the continents containing either '''Argentina''' or '''Australia'''. Order by name of the country.</p>
+
<p class='imper'>在阿根廷'''Argentina''' 及 澳大利亞 '''Australia'''所在的洲份中,列出當中的國家名字 '''name''' 及洲分 '''continent''' 。按國字名字順序排序</p>
  
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
Line 83: Line 83:
  
 
<div class='qu'>
 
<div class='qu'>
<p class='imper'>Which country has a population that is more than Canada but less than Poland? Show the name and the population.</p>
+
<p class='imper'>哪一個國家的人口比加拿大Canada的多,但比波蘭Poland的少?列出國家名字name和人口population 。</p>
  
 
<source lang='sql' class='def'></source>
 
<source lang='sql' class='def'></source>
Line 98: Line 98:
  
 
<div class='qu'>
 
<div class='qu'>
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.
+
Germany德國(人口8000萬),在Europe歐洲國家的人口最多。Austria奧地利(人口850萬)擁有德國總人口的11%。
<p class='imper'>Show the name and the population of each country in Europe. Show the population as a percentage of the population of Germany.</p>
+
<p class='imper'>顯示歐洲的國家名稱name和每個國家的人口population。以德國的人口的百分比作人口顯示。</p>
<div class=hint title='Decimal places'>You can use the function [[ROUND]] to remove the decimal places.</div>
+
 
<div class=hint title='Percent symbol %'>You can use the function [[CONCAT]] to add the percentage symbol.</div>
+
(population 80 million) has the largest population of the countries in . (population 8.5 million) has 11% of the population of Germany.
 +
Show the and the of each country in Europe. Show the population as a percentage of the population of Germany.
 +
<div class=hint title='小數位數'>您可以使用函數[[ROUND]] 刪除小數。</div>
 +
<div class=hint title='百分號 %'>您可以使用函數 [[CONCAT]] 增加的百分比符號。 </div>
 
<source lang='sql' class='def'></source>
 
<source lang='sql' class='def'></source>
  
Line 117: Line 120:
 
To gain an absurdly detailed view of one insignificant feature of the language, read on.
 
To gain an absurdly detailed view of one insignificant feature of the language, read on.
  
We can use the word <code>ALL</code> to allow >= or > or < or <=to act over a list. For example, you can find the largest country in the world, by population with this query:
+
我們可以用<code>ALL</code> 這個詞對一個列表進行>=><<=充當比較。例如,你可以用此查詢找到世界上最大的國家(以人口計算):
 
<source lang=sql>
 
<source lang=sql>
 
SELECT name
 
SELECT name
Line 125: Line 128:
 
                           WHERE population>0)
 
                           WHERE population>0)
 
</source>
 
</source>
You need the condition '''population>0''' in the sub-query as some countries have '''null''' for population.
+
你需在子查詢的條件中使用 '''population>0''',因為有些國家的記錄中,人口是沒有填入,只有 '''null'''值。
 
 
 
<div class='qu'>
 
<div class='qu'>
<p class='imper'>Which countries have a GDP greater than every country in Europe? [Give the '''name''' only.] (Some countries may have NULL gdp values) </p>
+
<p class='imper'>哪些國家的GDP比Europe歐洲的全部國家都要高呢? [只需列出 '''name''' ] (有些國家的記錄中,GDP是NULL,沒有填入資料的。) </p>
  
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
Line 143: Line 145:
 
</div>
 
</div>
  
We can refer to values in the outer SELECT within the inner SELECT. We can name the tables so that we can tell the difference between the inner and outer versions.
+
我們可以在子查詢,參閱外部查詢的數值。我們為表格再命名,便可以分別內外兩個不同的表格。
  
 
<div class='qu'>
 
<div class='qu'>
<p class='imper'>Find the largest country (by area) in each continent, show the '''continent''', the '''name''' and the '''area''': </p>
+
<p class='imper'>在每一個州中找出最大面積的國家,列出洲份 '''continent''', 國家名字 '''name''' 及面積 '''area'''。 (有些國家的記錄中,AREA是NULL,沒有填入資料的。)  </p>
  
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
Line 168: Line 170:
  
 
<div class='qu'>
 
<div class='qu'>
<p class='imper'>List each continent and the name of the country that comes first alphabetically.</p>
+
<p class='imper'>列出洲份名稱,和每個洲份中國家名字按子母順序是排首位的國家名。(即每洲只有列一國)</p>
  
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
Line 182: Line 184:
 
</div>
 
</div>
  
==Difficult Questions That Utilize Techniques Not Covered In Prior Sections==
+
==困難的題目(早前練習沒有包含的技巧)==
 
<div class='qu'>
 
<div class='qu'>
<p class='imper'>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'''. </p>
+
<p class='imper'>找出洲份,當中全部國家都有少於或等於 25000000 人口. 在這些洲份中,列出國家名字'''name''''''continent''' 洲份和'''population'''人口。 </p>
  
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
Line 200: Line 202:
  
 
<div class='qu'>
 
<div class='qu'>
<p class='imper'>Some countries have populations more than three times that of any of their neighbours (in the same continent). Give the countries and continents.</p>
+
<p class='imper'>有些國家的人口是同洲份的所有其他國的3倍或以上。列出 國家名字name 和 洲份 continent。</p>
  
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>

Revision as of 17:03, 26 March 2016

此教程教我們在SELECT查詢中使用別一個SELECT查詢,進行一些更複雜的查詢。

name國家名continent洲份area面積 population人口gdp國民生產總值
AfghanistanAsia6522302550010020343000000
AlbaniaEurope28748 2831741 12960000000
AlgeriaAfrica2381741 37100000 188681000000
AndorraEurope46878115 3712000000
AngolaAfrica1246700 20609294 100990000000
...

Using nested SELECT

練習

列出每個國家的名字 name,當中人口 population 是高於俄羅斯'Russia'的人口。

world(name, continent, area, population, gdp)


SELECT name FROM world
  WHERE population >
     (SELECT population FROM world
      WHERE name='Romania')
SELECT name FROM world
  WHERE population >
     (SELECT population FROM world
      WHERE name='Russia')


列出歐州每國家的人均GDP,當中人均GDP要高於英國'United Kingdom'的數值。

人均GDP即是 gdp除以population
SELECT name FROM world
  WHERE continent='Europe' AND gdp/population >
     (SELECT gdp/population FROM world
      WHERE name='United Kingdom')

在阿根廷Argentina 及 澳大利亞 Australia所在的洲份中,列出當中的國家名字 name 及洲分 continent 。按國字名字順序排序

SELECT name,continent
FROM world
WHERE continent IN (
  SELECT continent
  FROM world
  WHERE name IN ('Australia','Argentina'))
ORDER BY name


哪一個國家的人口比加拿大Canada的多,但比波蘭Poland的少?列出國家名字name和人口population 。

SELECT name,population FROM world
WHERE population BETWEEN
(SELECT population+1 FROM world WHERE name='Canada')
AND
(SELECT population-1 FROM world WHERE name='Poland')


Germany德國(人口8000萬),在Europe歐洲國家的人口最多。Austria奧地利(人口850萬)擁有德國總人口的11%。

顯示歐洲的國家名稱name和每個國家的人口population。以德國的人口的百分比作人口顯示。

(population 80 million) has the largest population of the countries in .  (population 8.5 million) has 11% of the population of Germany.

Show the and the of each country in Europe. Show the population as a percentage of the population of Germany.

您可以使用函數ROUND 刪除小數。
您可以使用函數 CONCAT 增加的百分比符號。
SELECT name,
   CONCAT(ROUND(100*population/(SELECT population FROM world WHERE name='Germany')),'%')
FROM world
WHERE continent='Europe'


To get a well rounded view of the important features of SQL you should move on to the next tutorial concerning aggregates.

To gain an absurdly detailed view of one insignificant feature of the language, read on.

我們可以用ALL 這個詞對一個列表進行>=或>或<或<=充當比較。例如,你可以用此查詢找到世界上最大的國家(以人口計算):

SELECT name
  FROM world
 WHERE population >= ALL(SELECT population
                           FROM world
                          WHERE population>0)

你需在子查詢的條件中使用 population>0,因為有些國家的記錄中,人口是沒有填入,只有 null值。

哪些國家的GDP比Europe歐洲的全部國家都要高呢? [只需列出 name 。] (有些國家的記錄中,GDP是NULL,沒有填入資料的。)

SELECT name FROM world 
  WHERE gdp > ALL
   (SELECT gdp FROM world
    WHERE continent = 'Europe' 
      AND gdp IS NOT NULL)

我們可以在子查詢,參閱外部查詢的數值。我們為表格再命名,便可以分別內外兩個不同的表格。

在每一個州中找出最大面積的國家,列出洲份 continent, 國家名字 name 及面積 area。 (有些國家的記錄中,AREA是NULL,沒有填入資料的。)

SELECT continent, name, population FROM world x
  WHERE population >= ALL
    (SELECT population FROM world y
        WHERE y.continent=x.continent
          AND population>0)
SELECT continent, name, area 
 FROM world x
  WHERE area >= ALL
    (SELECT area FROM world y
        WHERE y.continent=x.continent
         and area > 0 )


列出洲份名稱,和每個洲份中國家名字按子母順序是排首位的國家名。(即每洲只有列一國)

SELECT continent,name FROM world x
  WHERE x.name <= ALL (
    SELECT name FROM world y
     WHERE x.continent=y.continent)

困難的題目(早前練習沒有包含的技巧)

找出洲份,當中全部國家都有少於或等於 25000000 人口. 在這些洲份中,列出國家名字namecontinent 洲份和population人口。

SELECT name,continent,population FROM world x
  WHERE 25000000 >= ALL (
    SELECT population FROM world y
     WHERE x.continent=y.continent
       AND y.population>0)

有些國家的人口是同洲份的所有其他國的3倍或以上。列出 國家名字name 和 洲份 continent。

SELECT name, continent FROM world x WHERE
 population > ALL
 (SELECT population*3 FROM world y
 WHERE y.continent = x.continent
 AND y.name != x.name)