一道面试题

Question

现如下一张表:
ID YEAR                      MONTH                     ACOUNT
---------- ------------------------- ------------------------- -------------------------
1 1991                      1                         1.1
2 1991                      2                         1.2
3 1991                      3                         1.3
4 1992                      1                         1.1
5 1992                      2                         1.2
6 1992                      3                         1.3
---------------------------------------------------------------------------------------
查询出以下结果:
year         m1               m2                 m3
1991         1.1              1.2                1.3
1992         1.1              1.2                1.3小弟不知，希望大家能帮忙解答下，谢谢

Answer 1

select year,sum(case when month=1 then acount else 0 end) m1,
sum(case when month=2 then acount else 0 end) m2,
sum(case when month=3 then acount else 0 end) m3
from tb
group by year;

Answer 2

select a.year,a.account,b.account,c.account
  from 表 a, 表  b, 表  c
 where a.year = b.year
 and b.year = c.year
 and a.month< b.month
 and b.month< c.month
 and (a.id = 1 or a.id = 4)
谁出的变态题目啊·要自连三把！！！！

Answer 3

select YEAR,decode(month,'1',amount) m1,decode(month,'2',amount) m2,decode(month,'3',amount) m3
group by year