关于C#做月统计思路方法
最近想做一个月报表统计项目的应用程序,根据ACCESS数据库表里的数据统计一个月每天3个时段酒精、桔水这两种物资重量和罐数(一条记录表示一罐)。统计每天酒精和桔水分别称量的合计重量,最后月的合计统计。数据库表记录:
要实现报表打印的格式
哪位大侠指点一下要怎样实现统计的方法,大概思路? 报表 月统计 --------------------编程问答-------------------- 一般报表的分组功能就能实现 --------------------编程问答-------------------- 自己写SQL搞定吧 --------------------编程问答-------------------- sql or linq --------------------编程问答-------------------- sql 聚合函数就能搞定。 --------------------编程问答-------------------- 除 --------------------编程问答-------------------- 看结果:
数据表结构:
sql代码:
--说明
--Num质量 Qua数量 jj酒精 ju桔水
select a2,
(select sum(a4) from Table_4 t2 where t1.a2=t2.a2 and a6='酒精' and t2.a3<DATEADD(hh,8,CONVERT(datetime,t2.a2))) as Numjj8,
(select sum(a7) from Table_4 t2 where t1.a2=t2.a2 and a6='酒精' and t2.a3<DATEADD(hh,8,CONVERT(datetime,t2.a2))) as Quajj8,
(select sum(a4) from Table_4 t2 where t1.a2=t2.a2 and a6='桔水' and t2.a3<DATEADD(hh,8,CONVERT(datetime,t2.a2))) Numjs8,
(select sum(a7) from Table_4 t2 where t1.a2=t2.a2 and a6='桔水' and t2.a3<DATEADD(hh,8,CONVERT(datetime,t2.a2))) Quajs8,
(select sum(a4) from Table_4 t2 where t1.a2=t2.a2 and a6='酒精' and t2.a3 between DATEADD(hh,8,CONVERT(datetime,t2.a2)) and DATEADD(hh,16,CONVERT(datetime,t2.a2))) Numjj16,
(select sum(a7) from Table_4 t2 where t1.a2=t2.a2 and a6='酒精' and t2.a3 between DATEADD(hh,8,CONVERT(datetime,t2.a2)) and DATEADD(hh,16,CONVERT(datetime,t2.a2))) Quajj16,
(select sum(a4) from Table_4 t2 where t1.a2=t2.a2 and a6='桔水' and t2.a3 between DATEADD(hh,8,CONVERT(datetime,t2.a2)) and DATEADD(hh,16,CONVERT(datetime,t2.a2))) Numjs16,
(select sum(a7) from Table_4 t2 where t1.a2=t2.a2 and a6='桔水' and t2.a3 between DATEADD(hh,8,CONVERT(datetime,t2.a2)) and DATEADD(hh,16,CONVERT(datetime,t2.a2))) Quajs16,
(select sum(a4) from Table_4 t2 where t1.a2=t2.a2 and a6='酒精' and t2.a3 between DATEADD(hh,16,CONVERT(datetime,t2.a2)) and DATEADD(hh,24,CONVERT(datetime,t2.a2))) Numjj24,
(select sum(a7) from Table_4 t2 where t1.a2=t2.a2 and a6='酒精' and t2.a3 between DATEADD(hh,16,CONVERT(datetime,t2.a2)) and DATEADD(hh,24,CONVERT(datetime,t2.a2))) Quajj24,
(select sum(a4) from Table_4 t2 where t1.a2=t2.a2 and a6='桔水' and t2.a3 between DATEADD(hh,16,CONVERT(datetime,t2.a2)) and DATEADD(hh,24,CONVERT(datetime,t2.a2))) Numjs24,
(select sum(a7) from Table_4 t2 where t1.a2=t2.a2 and a6='桔水' and t2.a3 between DATEADD(hh,16,CONVERT(datetime,t2.a2)) and DATEADD(hh,24,CONVERT(datetime,t2.a2))) Quajs24,
(select sum(a4) from Table_4 t2 where t1.a2=t2.a2 and a6='酒精') Numjj,
(select sum(a7) from Table_4 t2 where t1.a2=t2.a2 and a6='酒精') Quajj,
(select sum(a4) from Table_4 t2 where t1.a2=t2.a2 and a6='桔水') Numjs,
(select sum(a7) from Table_4 t2 where t1.a2=t2.a2 and a6='桔水') Quajs from Table_4 t1 group by a2
这就完成一半工作了 --------------------编程问答-------------------- 这样子写法,应该。。。。。
补充:.NET技术 , 其他语言