面試到的SQL題

怎么把這樣一個表

year	month	amount
1991	1	1.1
1991	2	1.2
1991	3	1.3
1991	4	1.4
1992	1	2.1
1992	2	2.2
1992	3	2.3
1992	4	2.4

查询這樣一個結果

year	m1	m2	m3	m4
1991	1.1	1.2	1.3	1.4
1992	2.1	2.2	2.3	2.4

解答一

select year,
sum(case month when '1' then amount end) m1,
sum(case month when “2” then amount end) m2,
sum(case month when “3” then amount end) m3,
sum(case month when “4” then amount end) m4 
from table group by year order by year

解答二

1
2
3

select year,
group_concat(amount) 
from table group by year order by year

解答三

select p1.year,p1.month,p1.amount,p2.amount,p3.amount,p4.amount
from protest p1 INNER JOIN protest p2 ON p1.month = p2.month +1 INNER JOIN protest p3 ON p2.month = p3.month+1 INNER JOIN protest p4 ON p3.month = p4.month + 1 
WHERE p1.year = p2.year
AND p2.year = p3.year AND p3.year = p4.year;