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

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

--显示当月(7月)汇总数据,1-31的每天数据

select   b.procdno,b.prodname,(A1+A2+A3+A4+A5+A6+A7+A8+A9+A10+A11+A12+A13+A14+A15+A16+A17+A18+A19+A20+A21+A22+A23+A24+A25+A26+A27+A28+A29+A30+A31)   AS   total,A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12,A13,A14,A15,A16,A17,A18,A19,A20,A21,A22,A23,A24,A25,A26,A27,A28,A29,A30,A31   into   CCC   from   AAA   a   left   join   BBB   b   on   a.prodno=b.prodno   order   by   b.prodno

--从engproc提取相同的记录到DDD表

select   prodno,procname   into   DDD   from   engproc   where   prodno   in   (select   prodno   form   CCC)

insert   into   CCC(prodno,procname)   select   prodno,procname   from   DDD   where   not   exists   (select   *   from   CCC   where   ccc.prodno=DDD.prodno)

--显示当月数据

select   *   from   CCC

------解决方案--------------------

------解决方案--------------------

select procdno,prodname,sum(hgp) as total,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 CCC from scjd where inputdate between '2007-7-1 ' and '2007-7-31 ' group by procdno,prodname order by procdno

--从engproc提取相同的记录到DDD表

select prodno,procname into DDD from engproc where prodno in (select prodno form CCC)