表名 test
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
oracle
select year
,sum(decode(month,1,amount,0)) m1
,sum(decode(month,2,amount,0)) m2
,sum(decode(month,3,amount,0)) m3
,sum(decode(month,4,amount,0)) m4
from test
group by year;
mysql
select year ,
max(case when month='1' then amount else null end) m1,
max(case when month='2' then amount else null end) m2,
max(case when month='3' then amount else null end) m3,
max(case when month='4' then amount else null end) m4
from test group by year;