需求:--按月份统计 2012 年各个地区的水费
select (select name from t_area where id= areaid) 区名,
sum(case when month='01' then money else 0 end) 一月,
sum(case when month='02' then money else 0 end) 二月,
sum(case when month='03' then money else 0 end) 三月,
sum(case when month='04' then money else 0 end) 四月,
sum(case when month='05' then money else 0 end) 五月,
sum(case when month='06' then money else 0 end) 六月,
sum(case when month='07' then money else 0 end) 七月,
sum(case when month='08' then money else 0 end) 八月,
sum(case when month='09' then money else 0 end) 九月,
sum(case when month='10' then money else 0 end) 十月,
sum(case when month='11' then money else 0 end) 十一月,
sum(case when month='12' then money else 0 end) 十二月
from t_account group by areaid;
结果:
需求:按季度统计 2012 年各个地区的水费
select (select name from T_AREA where id= areaid ) 区域,
sum( case when month>='01' and month<='03' then money else
0 end) 第一季度,
sum( case when month>='04' and month<='06' then money else
0 end) 第二季度,
sum( case when month>='07' and month<='09' then money else
0 end) 第三季度,
sum( case when month>='10' and month<='12' then money else
0 end) 第四季度
from T_ACCOUNT where year='2012' group by areaid
结果:
通过这种方式可以做日报,月报表,季度报表,年度报表