# 有关数据汇总-统计-交叉表的再优化(请进讨论)解决思路

www.myexceptions.net  网友分享于：2013-02-16  浏览：18次

--汇总当月(7月)的数据

select   inputdate,procdno,prodname,sum(hgp)   as   hgp,sum(blp)   as   blp,sum(fp)   as   fp   into   AAA   from   scjd   where   inputdate   between   '2007-7-1 '   and   '2007-7-31 '   group   by   inputdate,procdon,prodname

--分别汇总1-31号每天的数据

select   inputdate,procdno,prodname,sum(case   inputdate   when   '2007-7-1 '   then   hgp   esle   0   end)   as   A1,sum(case   inputdate   when   '2007-7-2 '   then   hgp   esle   0   end)   as   A2,sum(case   inputdate   when   '2007-7-3 '   then   hgp   else   0   end   )   as   A3,
sum(case   inputdate   when   '2007-7-4 '   then   hgp   esle   0   end   )   A4,sum(case   inputdate   when '2007-7-5 '   then   hgp   else   0   end   )   as   A5,sum(case   inputdate   when   '2007-7-6 '   then   hgp   else   0   end   )   as   A6,sum(case   inputdate   when   '2007-7-7 '   then   hgp   else   0   end   )   as   A7,sum(case   inputdate   when   '2007-7-8 '   then   hgp   else   0   end   )   as   A8,sum(case   inputdate   when   '2007-7-9 '   then   hgp   else   0   end   )   as   A9,sum(case   inputdate   when   '2007-7-10 '   then   hgp   else   0   end   )   as   A10,sum(case   inputdate   when   '2007-7-11 '   then   hgp   esle   0   end)   as   A11,sum(case   inputdate   when   '2007-7-12 '   then   hgp   esle   0   end)   as   A12,sum(case   inputdate   when   '2007-7-13 '   then   hgp   else   0   end   )   as   A13,sum(case   inputdate   when   '2007-7-14 '   then   hgp   esle   0   end   )   A14,sum(case   inputdate   when   '2007-7-15 '   then   hgp   else   0   end   )   as   A15,sum(case   inputdate   when   '2007-7-16 '   then   hgp   else   0   end   )   as   A16,sum(case   inputdate   when   '2007-7-17 '   then   hgp   else   0   end   )   as   A17,sum(case   inputdate   when   '2007-7-18 '   then   hgp   else   0   end   )   as   A18,sum(case   inputdate   when   '2007-7-19 '   then   hgp   else   0   end   )   as   A19,sum(case   inputdate   when   '2007-7-20 '   then   hgp   else   0   end   )   as   A20,sum(case   inputdate   when   '2007-7-10 '   then   hgp   else   0   end   )   as   A10,sum(case   inputdate   when   '2007-7-21 '   then   hgp   esle   0   end)   as   A21,sum(case   inputdate   when   '2007-7-22 '   then   hgp   esle   0   end)   as   A22,sum(case   inputdate   when   '2007-7-23 '   then   hgp   else   0   end   )   as   A23,sum(case   inputdate   when   '2007-7-24 '   then   hgp   esle   0   end   )   A24,sum(case   inputdate   when   '2007-7-25 '   then   hgp   else   0   end   )   as   A25,sum(case   inputdate   when   '2007-7-26 '   then   hgp   else   0   end   )   as   A26,sum(case   inputdate   when   '2007-7-27 '   then   hgp   else   0   end   )   as   A27,sum(case   inputdate   when   '2007-7-28 '   then   hgp   else   0   end   )   as   A28,sum(case   inputdate   when   '2007-7-29 '   then   hgp   else   0   end   )   as   A29,sum(case   inputdate   when   '2007-7-30 '   then   hgp   else   0   end   )   as   A30,sum(case   inputdate   when   '2007-7-31 '   then   hgp   else   0   end   )   as   A31   into   BBB   from   AAA   group   by   procdno,prodname   order   by   procdno