好题分享、心路历程（力扣618）—— case when

【题目介绍】

该题为力扣618，名为学生地理信息报告。

【题型分类】

属于 case when 专题。

官网标为困难题，符合。

【思路分享】

这里先分享 where 过滤的等价写法。

关键点在于创建序号，以相同序号连表。

题解：

with tmp1 as
(select name,row_number() over(order by name) as rnk1
from Student 
where continent = 'America'
order by name)

,tmp2 as
(select name,row_number() over(order by name) as rnk2
from Student 
where continent = 'Asia'
order by name)

,tmp3 as
(select name,row_number() over(order by name) as rnk3
from Student 
where continent = 'Europe'
order by name)

,tmp4 as
(select tmp1.name as America,
    tmp2.name as Asia,rnk1
from tmp1 left join tmp2
on rnk1 = rnk2)

select America,Asia,name as Europe
from tmp4 left join tmp3
on rnk1 = rnk3

-END

https://leetcode.cn/problems/students-report-by-geography/solution/xue-sheng-di-li-xin-xi-bao-gao-by-leetcode/