case when then以及分组求TopN练习

准备数据，关于姓名学科成绩的表

score.name      score.subject   score.score
孙悟空              语文             87
孙悟空              数学             95
孙悟空              英语             68
大海                语文             94
大海                数学             56
大海                英语             84
宋宋                语文             64
宋宋                数学             86
宋宋                英语             84
婷婷                语文             65
婷婷                数学             85
婷婷                英语             78

需求1：求每门学科排前两名的同学

首先我们应该按照科目分组，成绩倒序排序，用rank()函数查出每门学科里同学的一个成绩排序

select
subject,
name,
score,
rank() over(partition by subject order by score desc) score_rank
from 
score;

再根据where进行上表筛选，将score_rank小于等于2的过滤掉

select
subject,
name,
score,
score_rank
from 
(select
subject,
name,
score,
rank() over(partition by subject order by score desc) score_rank
from 
score)t1 where score_rank <= 2;

需求2：输出格式为：

name    math    chinese english
大海    56         94      84
婷婷    85         65      78
孙悟空  95         87      68
宋宋    86         64      84

用case when then

select
name,
sum(case when subject='数学' then score end) as Math,
sum(case when subject='语文' then score end) as Chinese,
sum(case when subject='英语' then score end) as English
from
score
group by name;

 

一，已知数据按照另外一种方式进行分组，分析。 

有如下数据:(为了看得更清楚，我并没有使用国家代码，而是直接用国家名作为primary key)

国家（country）	人口（population）
中国	600
美国	100
加拿大	100
英国	200
法国	300
日本	250
德国	200
墨西哥	50
印度	250

根据这个国家人口数据，统计亚洲和北美洲的人口数量。应该得到下面这个结果。

洲	人口
亚洲	1100
北美洲	250
其他	700

想要解决这个问题，你会怎么做？生成一个带有洲code的view，是一个解决方法，但是这样很难动态的改变统计的方式。 
假如使用case函数，sql代码如下:

select
case COUNTRY
when '中国' then '亚洲'
when '日本' then '亚洲'
when '墨西哥' then '北美洲'
when '美国' then '北美洲'
when '加拿大' then '北美洲'
else '其他' end COUNTRY,
sum(POPULATION) POPULATION
from test
group by
case COUNTRY
when '中国' then '亚洲'
when '日本' then '亚洲'
when '墨西哥' then '北美洲'
when '美国' then '北美洲'
when '加拿大' then '北美洲'
else '其他' end order by POPULATION;