等级：困难

题目

  1. drop table student
  2. create Table student (name varchar(25),continent varchar(25));
  4. insert into student values('Jack','America')
  5. insert into student values('Pascal','Europe')
  6. insert into student values('Xi','Asia')
  7. insert into student values('Jane','America')

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

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.

Follow-up: If it is unknown which continent has the most students, can you write a query to generate the student report?

大致意思是：旋转这张表，列变行，达成下面的效果。你并不知道有多少学生。

  1. --Before
  2. | name   | continent |
  3. |--------|-----------|
  4. | Jack   | America   |
  5. | Pascal | Europe    |
  6. | Xi     | Asia      |
  7. | Jane   | America   |
  9. --After
  10. | America | Asia | Europe |
  11. |---------|------|--------|
  12. | Jack    | Xi   | Pascal |
  13. | Jane    |      |        |

思路

按大洲分组，然后给给每个学生加上一个编号，跟 row_number() 的思路一样，然后再 FULL JOIN，大致形成这样一个表：
image.png

这就产生了一个问题，MySQL 中没有 row_number() 那么好用的开窗函数，怎么办？🙂