Students Report By Geography Problem

Description

LeetCode Problem 618.

A U.S graduate school has students from Asia, Europe and America. The students’ location information are stored in table student as below.

| name   | continent |
|--------|-----------|
| Jack   | America   |
| Pascal | Europe    |
| Xi     | Asia      |
| Jane   | America   |

Pivot the continent column in this table so that each name is sorted alphabetically and displayed underneath its corresponding continent. The output headers should be America, Asia and Europe respectively. It is guaranteed that the student number from America is no less than either Asia or Europe.

For the sample input, the output is:

| America | Asia | Europe |
|---------|------|--------|
| Jack    | Xi   | Pascal |
| Jane    |      |        |

MySQL Solution

select America, Asia, Europe
from 
    (select name as America, row_number() over () as rn
    from student
    where continent = 'America'
    order by America) a
left join     
    (select name as Asia, row_number() over () as tn
    from student
    where continent = 'Asia'
    order by Asia)  b
on rn = tn    
left join
    (select name as Europe, row_number() over () as kn
    from student
    where continent = 'Europe'
    order by Europe)  c    
on rn = kn

28 Dec 2020

Database

« Report Contiguous Dates Problem Tournament Winners Problem »

LeetCode Database - Hard LeetCode 185 LeetCode 262 LeetCode 569 LeetCode 571 LeetCode 579 LeetCode 601 LeetCode 615 LeetCode 1097 LeetCode 1127 LeetCode 1159 LeetCode 1194 LeetCode 1225 LeetCode 1336 LeetCode 1369 LeetCode 1384 LeetCode 1412 LeetCode 1479 LeetCode 1635 LeetCode 1645 LeetCode 1651 LeetCode 1767 LeetCode 1892 LeetCode 1917 LeetCode 1919 LeetCode 1972 More LeetCode Database MySQL Tutorials