今天筆試中的SQL題

表結構和資料
year month amount
1991   1    1.1
1991   2    1.2
1991   3    1.3
1991   4    1.4
1992   1    4.6
1992   2    1.5
1992   3    2.4
1992   4    2.2
查詢結果
year m1   m2   m3   m4
1991 1.1  1.2  1.3  1.4
1992 4.6  1.5  2.4  2.2
依據結果寫SQL語句。

答案 ：

 select a.[year],a.amount as m1,b.amount as m2,c.amount as m3,d.amount as m4

from table1 as a,table1 as b,table1 as c,table1 as d where

a.[month]=b.[month] and b.[month]=c.[month] and c.[month]=d.[month]

and a.[year]=b.[year] and b.[year]=c.[year] and c.[year]=d.[year]
order by a.[year]