mysql---SQLZOO：从WORLD选择教程/ zh

在这教程中，我们会使用SELECT语句，对World表格进行查询。

---

 

Show the name and the continent - but substitute Eurasia for Europe and Asia; substitute America - for each country in North America or South America or Caribbean. Show countries beginning with A or B

显示名称和大陆 - 但以欧亚和欧洲替代欧洲和亚洲; 替代美国 - 北美 或南美洲或加勒比地区的每个国家。显示以A或B开头的国家

SELECT name,
CASE WHEN continent IN(‘Europe‘,‘Asia‘)
     THEN ‘Eurasia‘
     WHEN continent IN(‘North America‘,‘South America‘,‘Caribbean‘)
     THEN ‘America‘
     ELSE continent END
FROM world
WHERE name LIKE ‘A%‘ OR name LIKE ‘B%‘;

---

Put the continents right...

• Oceania becomes Australasia
• Countries in Eurasia and Turkey go to Europe/Asia
• Caribbean islands starting with ‘B‘ go to North America, other Caribbean islands go to South America

Show the name, the original continent and the new continent of all countries.

把大陆放在正确的位置...

• 大洋洲成为大洋洲
• 欧亚和土耳其的国家去欧洲/亚洲
• 以‘B‘ 开头的加勒比群岛去北美，其他加勒比群岛去南美洲

显示所有国家的名称，原始大陆和新大陆。  

SELECT name,continent,
CASE WHEN continent IN (‘Eurasia‘, ‘Turkey‘)
     THEN ‘Europe/Asia‘
     WHEN continent = ‘Oceania‘ 
     THEN ‘Australasia‘
     WHEN continent = ‘Caribbean‘
          THEN
          CASE 
          WHEN name LIKE ‘B%‘ 
          THEN ‘North America‘
          ELSE ‘South America‘
          END
     ELSE continent 
     END
FROM world
ORDER BY name ASC;

 

 

mysql---SQLZOO：从WORLD选择教程/ zh

标签：image   section   mysq   lan   begin   sub   表格   country   else