Question：
一所美國大學(xué)有來自亞洲、歐洲和美洲的學(xué)生，他們的地理信息存放在如下student表中。

create table student618(
name varchar(20),
continent varchar(20));

insert into student618 VALUES 
('Jack','America'),
('Pascal','Europe'),
('Xi','Asia'),
('Jane','America');

寫一個查詢語句實現(xiàn)對大洲（continent）列的?透視表?操作，使得每個學(xué)生按照姓名的字母順序依次排列
在對應(yīng)的大洲下面。如圖：

image.png

方法一：利用變量@+3次join

select America,Asia,Europe
from 
        (select @num:=@num+1 as id,name as America
         from student618,(select @num:=0) t1
         where continent='America'
         order by name) a 
left join 
         (select @num1:=@num1+1 as id,name as Asia
         from student618,(select @num1:=0) t1
         where continent='Asia'
         order by name) b
on a.id = b.id
left join 
         (select @num2:=@num2+1 as id,name as Europe
         from student618,(select @num2:=0) t1
         where continent='Europe'
         order by name) c
on a.id = c.id

方法二：利用開窗函數(shù)row_number和3次join

select America,Asia,Europe
from 
        (select row_number() over(partition by continent order by name) as amid,name as America
        from student618
        where continent='America') a
left join
        (select row_number() over(partition by continent order by name) as asid,name as Asia
        from student618
        where continent='Asia') b
on a.amid = b.asid
left join
        (select row_number() over(partition by continent order by name) as euid,name as Europe
        from student618
        where continent='Europe') c
on a.amid = c.euid

此題的難點：
1、第一次看到輸出結(jié)果時，第一反應(yīng)是用case when來作答，但是用如下語句運行后：

select case continent when 'America' then name end as America,
             case continent when 'Europe' then name end as Europe,
             case continent when 'Asia' then name end as Asia
from student618

image.png

在原表中沒有多余的列進行g(shù)roup by來對case when后的字段進行聚合，所以，依據(jù)case when 一行一行的來執(zhí)行，就會得到如圖所示的結(jié)果，非所需答案。

2、在進行變量@解答時，left join后的前后兩個表的變量設(shè)置不能用同一個變量名稱@num，如果用同一個@num的話，join后面的表會在前面的基礎(chǔ)上進行變量增加，無法得到想要的自增序列。