select id,substring(month, 1, 4) as year,
sum(case substring(month, 5, 2) when '01' then pay else 0 end) as M1,
sum(case substring(month, 5, 2) when '02' then pay else 0 end) as M2,
sum(case substring(month, 5, 2) when '03' then pay else 0 end) as M3,
sum(case substring(month, 5, 2) when '04' then pay else 0 end) as M4,
sum(case substring(month, 5, 2) when '05' then pay else 0 end) as M5,
sum(case substring(month, 5, 2) when '06' then pay else 0 end) as M6,
sum(case substring(month, 5, 2) when '07' then pay else 0 end) as M7,
sum(case substring(month, 5, 2) when '08' then pay else 0 end) as M8,
sum(case substring(month, 5, 2) when '09' then pay else 0 end) as M9,
sum(case substring(month, 5, 2) when '10' then pay else 0 end) as M10,
sum(case substring(month, 5, 2) when '11' then pay else 0 end) as M11,
sum(case substring(month, 5, 2) when '12' then pay else 0 end) as M12
from table1
group by id,substring(month, 1, 4)
sum(case substring(month, 5, 2) when '01' then pay else 0 end) as M1,
sum(case substring(month, 5, 2) when '02' then pay else 0 end) as M2,
sum(case substring(month, 5, 2) when '03' then pay else 0 end) as M3,
sum(case substring(month, 5, 2) when '04' then pay else 0 end) as M4,
sum(case substring(month, 5, 2) when '05' then pay else 0 end) as M5,
sum(case substring(month, 5, 2) when '06' then pay else 0 end) as M6,
sum(case substring(month, 5, 2) when '07' then pay else 0 end) as M7,
sum(case substring(month, 5, 2) when '08' then pay else 0 end) as M8,
sum(case substring(month, 5, 2) when '09' then pay else 0 end) as M9,
sum(case substring(month, 5, 2) when '10' then pay else 0 end) as M10,
sum(case substring(month, 5, 2) when '11' then pay else 0 end) as M11,
sum(case substring(month, 5, 2) when '12' then pay else 0 end) as M12
from table1
group by id,substring(month, 1, 4)
你先建一个表,字段为,YEAR(年份)、MONTH1 到 MONTH12;(13个必要字段)
在存储过程中定义十三个变量;(用于放一条记录中每个月的PAY和年份)
用游标打开你的SELECT语句,小心啊,这个SELECT语句必须按你的年月排好序;
1、初始化MONTH1到MONTH12 这12个变量为0;
2、先把一条记录取出来,取得年份,给你的年份变量,对比你的月份是什么月的,再把PAY给这个月;
3、取下一条记录,取出年份,对比你的年份变量的值,相同就对它给对应的月份变量,不同,说明不是这年的了,而是另一年了了,把刚才的13个变量值(一个年份和12个月的PAY)当一记录插入到刚才所建的表,插完后,再把年份值给年份变量,PAY给对应的月份MONTH;
4又开始了下一年的处理!(循环下去)
我想是这种办法的效率是不错的了!只扫描一次表!
select id,substring(month, 1, 4) as year,
sum(case substring(month, 5, 2) when '01' then pay else 0 end) as M1,
sum(case substring(month, 5, 2) when '02' then pay else 0 end) as M2,
sum(case substring(month, 5, 2) when '03' then pay else 0 end) as M3,
sum(case substring(month, 5, 2) when '04' then pay else 0 end) as M4,
sum(case substring(month, 5, 2) when '05' then pay else 0 end) as M5,
sum(case substring(month, 5, 2) when '06' then pay else 0 end) as M6,
sum(case substring(month, 5, 2) when '07' then pay else 0 end) as M7,
sum(case substring(month, 5, 2) when '08' then pay else 0 end) as M8,
sum(case substring(month, 5, 2) when '09' then pay else 0 end) as M9,
sum(case substring(month, 5, 2) when '10' then pay else 0 end) as M10,
sum(case substring(month, 5, 2) when '11' then pay else 0 end) as M11,
sum(case substring(month, 5, 2) when '12' then pay else 0 end) as M12
from table1
group by id,substring(month, 1, 4)
where .......