日期:2014-05-18 浏览次数:20608 次
/* 按月进行行列转换并加合计(2007-11-19于海南三亚) 例如有表tb某些人每月消费数据如下: id data month 001 11 1 001 12 2 001 13 3 001 14 4 001 15 5 001 16 6 001 17 7 001 18 8 001 19 9 001 110 10 001 111 11 001 112 12 002 21 1 002 22 2 002 23 3 002 24 4 002 25 5 002 26 6 002 27 7 002 28 8 002 29 9 002 210 10 002 211 11 002 212 12 要实现如下结果: 人员 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月 001 11 12 13 14 15 16 17 18 19 110 111 112 002 21 22 23 24 25 26 27 28 29 210 211 212 */ create table tb ( id char(3), data int, month int ) insert into tb(id,data,month) values('001',11,1) insert into tb(id,data,month) values('001',12,2) insert into tb(id,data,month) values('001',13,3) insert into tb(id,data,month) values('001',14,4) insert into tb(id,data,month) values('001',15,5) insert into tb(id,data,month) values('001',16,6) insert into tb(id,data,month) values('001',17,7) insert into tb(id,data,month) values('001',18,8) insert into tb(id,data,month) values('001',19,9) insert into tb(id,data,month) values('001',110,10) insert into tb(id,data,month) values('001',111,11) insert into tb(id,data,month) values('001',112,12) insert into tb(id,data,month) values('002',21,1) insert into tb(id,data,month) values('002',22,2) insert into tb(id,data,month) values('002',23,3) insert into tb(id,data,month) values('002',24,4) insert into tb(id,data,month) values('002',25,5) insert into tb(id,data,month) values('002',26,6) insert into tb(id,data,month) values('002',27,7) insert into tb(id,data,month) values('002',28,8) insert into tb(id,data,month) values('002',29,9) insert into tb(id,data,month) values('002',210,10) insert into tb(id,data,month) values('002',211,11) insert into tb(id,data,month) values('002',212,12) go SELECT id as '人员' , SUM(CASE month WHEN 1 THEN data ELSE 0 END) AS '1月' , SUM(CASE month WHEN 2 THEN data ELSE 0 END) AS '2月' , SUM(CASE month WHEN 3 THEN data ELSE 0 END) AS '3月' , SUM(CASE month WHEN 4 THEN data ELSE 0 END) AS '4月' , SUM(CASE month WHEN 5 THEN data ELSE 0 END) AS '5月' , SUM(CASE month WHEN 6 THEN data ELSE 0 END) AS '6月' , SUM(CASE month WHEN 7 THEN data ELSE 0 END) AS '7月' , SUM(CASE month WHEN 8 THEN data ELSE 0 END) AS '8月' , SUM(CASE month WHEN 9 THEN data ELSE 0 END) AS '9月' , SUM(CASE month WHEN 10 THEN data ELSE 0 END) AS '10月' , SUM(CASE month WHEN 11 THEN data ELSE 0 END) AS '11月' , SUM(CASE month WHEN 12 THEN data ELSE 0 END) AS '12月' FROM tb GROUP BY ID drop table tb /* 人员 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月 ---- --- --- --- --- --- --- --- --- --- ---- ---- ---- 001 11 12 13 14 15 16 17 18 19 110 111 112 002 21 22 23 24 25 26 27 28 29 210 211 212 (所影响的行数为 2 行) */ -------------------------- /* 合计每个人每年的数据 人员 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月 合计 001 11 12 13 14 15 16 17 18 19 110 111 112 468 002 21 22 23 24 25 26 27 28 29 210 211 212 858 */ SELECT id as '人员' , SUM(CASE month WHEN 1 THEN data ELSE 0 END) AS '1月' , SUM(CASE month WHEN 2 THEN data ELSE 0 END) AS '2月' , SUM(CASE month WHEN 3 THEN data ELSE 0 END) AS '3月' , SUM(CASE month WHEN 4 THEN data ELSE 0 END) AS '4月' , SUM(CASE month WHEN 5 THEN data ELSE 0 END) AS '5月' , SUM(CASE month WHEN 6 THEN data ELSE 0 END) AS '6月' , SUM(CASE month WHEN 7 THEN data ELSE 0 END) AS '7月' , SUM(CASE month WHEN 8 THEN data ELSE 0 END) AS '8月' , SUM(CASE month WHEN 9 THEN data ELSE 0 END) AS '9月' , SUM(CASE month WHEN 10 THEN data ELSE 0 END) AS '10月' , SUM(CASE month WHEN 11 THEN data ELSE 0 END) AS '11月' , SUM(CASE month WHEN 12 THEN data ELSE 0 END) AS '12月' , SUM(data) as '合计' FROM tb GROUP BY ID /* 人员 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月 合计 ---- --- --- --- --- --- --- --- --- --- ---- ---- ---- ---- 001 11 12 13 14 15 16 17