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

【题目介绍】

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

【题型分类】

属于 case when 专题。

官网标为困难题，符合；其实是之前两道好题分享的变型，之前是数值类型，现在是文本型。

【思路分享】

方法一：case when

如果仅用 case when，会出现记录分散的情况，所以需要用聚合的方式合并。

这里文本形式的聚合，采用 max() 或者 min() 排空，不采用之前数值形式的 sum()。

另还有聚合技巧，row_number() 创建序号列，聚合用。

聚合前：

聚合后：

题解：

with tmp1 as
(select *,
    row_number() over(partition by continent order by name) as rnk
from Student)

tmp2 as
(select rnk,
    case when continent = 'America' then name end as America,
    case when continent = 'Asia' then name end as Asia,
    case when continent = 'Europe' then name end as Europe
from tmp1)

select max(America) as America,
    max(Asia) as Asia,
    max(Europe) as Europe
from tmp2
group by rnk

方法二：where、join

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

过滤后：

 

题解：

 

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/