SQLZOO- select in select Percentages of Germany 写后分析

Percentages of Germany

5.

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.

The format should be Name, Percentage for example:

name	percentage
Albania	3%
Andorra	0%
Austria	11%
...	...

Decimal places

You can use the function ROUND to remove the decimal places.

Percent symbol %

You can use the function CONCAT to add the percentage symbol.

首先

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

大框架是用select语句表达出来

即

SELECT
	name, percentage
FROM 
    world
WHERE  
    continent = 'Europe';
但是表中并无pprecentage，所以用从concat as 函数创建

---

SELECT
	name,
	concat （A,'%'） as percentage
FROM  
    world
WHERE  
    continent = 'Europe';

但是A是什么我们要进行解释,percentage= population of the population of Germany,A 就是这个小数转化为百分数的值，要怎么描述它？.

A-a1,a2,a3
首先 我们要表达出 population of Germany's
a1; population/(SELECT population FROM world WHERE name = 'Germany'

其次 这个值是一个小数，要保留两位小数（看题中的假设答案得）
用round 函数  round(number,digits)
a2; round(a1,2)
a2; round(population/(SELECT population FROM world
		      WHERE name = 'Germany'),2)

最后 要把其转化成另一种数据类型，小数变百分数，使用cast sth as int

a3; cast（a2*100 as int）
cast(round(population/(SELECT population FROM world
		      WHERE name = 'Germany'),2)*100 as int）

结合起来就是

SELECT
	name,
	concat(cast(round(population/(SELECT population FROM world
		      WHERE name = 'Germany'),2)*100 as int),'%') as percentage
FROM 
    world
WHERE 
    continent = 'Europe';