SQLZOO 中文第二套（困难版）

最新推荐文章于 2025-01-13 18:02:07 发布

原创最新推荐文章于 2025-01-13 18:02:07 发布 · 747 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#SQLZOO #答案 #二 #中文 #困难

SQL 专栏收录该内容

4 篇文章

订阅专栏

本文介绍使用SQL进行地理名称替换的方法，通过CASE WHEN语句实现针对特定条件下的大陆名称更改。适用于需要调整地理数据展示的应用场景。

11.Show the name - but substitute Australasia for Oceania - for countries beginning with N.

SELECT name, 
CASE 
WHEN continent='Oceania ' THEN 'Australasia'
ELSE continent 
END
FROM world
WHERE name like 'N%'

12.Show the name and the continent - but substitute Eurasia for Europe and Asia.

SELECT name, 
CASE WHEN continent = 'Europe' OR continent = 'Asia' THEN 'Eurasia'
WHEN continent = 'North America' OR continent = 'South America' OR continent = 'Caribbean' THEN 'America'
ELSE continent
END
FROM world WHERE name LIKE 'A%' OR name LIKE 'B%'

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

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