有关数据汇总-统计-交叉表的再优化(请进讨论)
下面是对当月的数据汇总显示,不知你可有好的方法,请你再帮优化一下,谢谢!
--汇总当月(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
--显示当月(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)
将相关数据加入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
[解决办法]
写这样的sql语句显得很笨重的方法,这类的处理办法应该是用动态的sql语句
随便在旧帖里搜索一下都可以找到动态sql语句的写法
[解决办法]
可以简化下
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)
将相关数据加入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
