算法的重要性,我就不多说了吧,想去大厂,就必须要经过基础知识和业务逻辑面试+算法面试。所以,为了提高大家的算法能力,这个公众号后续每天带大家做一道算法题,题目就从LeetCode上面选 !
今天和大家聊的问题叫做 学生地理信息报告,我们先来看题面:
https://leetcode.cn/problems/students-report-by-geography/
示例
解题
1.新增1列,给3个洲的学生分别按照姓名顺序排名,使用row_number() over (order by ) 函数,这样有3个表
2.将3个表按照排名列联接起来
select America, Asia, Europe
from
(select name as America,row_number() over (order by name ASC) as rn from student where continent='America') a
left join
(select name as Asia,row_number() over (order by name ASC) as rn from student where continent='Asia') b
on a.rn=b.rn
left join
(select name as Europe,row_number() over (order by name ASC) as rn from student where continent='Europe') c
on c.rn=a.rn
上期推文: