表结构及数据如下:
按月份统计结果:
select substr(syear,5,2) 月份,sum(smoney) 钱 from sellmoney group by substr(syear,5,2);
实现行列倒置:将月份由行置换成 每一列
(1)
select
sum(case when substr(syear,5,2)='01' then smoney end) as 一月,
sum(case when substr(syear,5,2)='02' then smoney end) as 二月,
sum(case when substr(syear,5,2)='03' then smoney end) as 三月,
sum(case when substr(syear,5,2)='04' then smoney end) as 四月
from sellmoney;
(2)
select
sum(decode(substr(syear,5,2),'01',smoney,0)) 一月,
sum(decode(substr(syear,5,2),'02',smoney,0)) 二月,
sum(decode(substr(syear,5,2),'03',smoney,0)) 三月,
sum(decode(substr(syear,5,2),'04',smoney,0)) 四月
from sellmoney;
结果如下: