数据库列转行显示。(oracle mysql)

表名 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;

 

posted @   open88  阅读(309)  评论(0编辑  收藏  举报
努力加载评论中...
点击右上角即可分享
微信分享提示