select 发货单位,sum(重量1) 重量1,sum(重量2) 重量2,sum(重量3) 重量3,sum(次数) 次数 from t group by 发货单位 order by min(日期时间) 报表里面页脚用SUM
select State,City,sum(W1) as W1,sum(W2) as W2,sum(W3) as W3,sum(Nums) as Nums ,Grouping(State) as States,Grouping(City) as Citys from TestTable where DT between '2012-03-01 00:00:00' and '2012-03-31 23:59:59' group by State,City with rollup当States为1时表示片区合计,Citys为1时表示单位合计。以上可以满足你的要求。100分吧。
刚先没看kaikai_kk的Sql语句,自己想了下,发现竟然和kaikai_kk想法一样呀,Grouping(City) as Citys,这种写法好像也不能达到效果吧,Grouping(City)只是返回了0或1的结果呀。小弟是新手,不对莫怪!
sql code 如下:select pianqu,fahuodanwei,sum(maozhong) as maozhong,sum(jingzhong) as jingzhong,sum(kcl) as kcl,sum(hangshu) as hangshu ,Grouping(pianqu) as pianqu,Grouping(fahuodanwei) as fahuodanwei from sysdb where gbsj between '2012-03-01 00:00:00' and '2012-03-31 23:59:59' group by pianqu,fahuodanwei with rollu 我得到的报表如下但我想改成如图所示应该怎么改? p
以下仅供参考如果你的表设计有规则有话,比如用编号表示会变得很简单,因为中文排序很差 片区编码固定长度4位:例如从A000开始 发货单位编码:片区+任意字符,例如是A000xxxx下面d是日期,a1是单位,a2片区,q1是重量(其它不列出), left(a1,4)中4表示片区编码的长度select a1,q1 from (select min(d) as d, a1,sum(q1) as q1 from t group by a1 union all select max(d) as d, a2,sum(q1) as q1 from t group by a2 union all select getdate()+2 as d,'ZZZZZZZZ' as a3,sum(q1) q1 from t) as X order by left(a1,4),d
select dw,sum(weight1),sum(weight2),sum(weight3),sum(cs) from table where time between time1 and time2 group by dw union all select zone as dw,sum(weight1),sum(weight2),sum(weight3),sum(cs) from table where time between time1 and time2 group by zonedw--单位 zone--片区 难道我理解错了?
理解正确,功能已经按Oraclers得代码实现,关键是报表里的格式问题,谢谢
select case when States=1 then N'总合计' else State end as State, case when Citys=1 then N'合计' else City end as Citys, W1,W2,W3,Nums from ( select State,City,sum(W1) as W1,sum(W2) as W2,sum(W3) as W3,sum(Nums) as Nums ,Grouping(State) as States,Grouping(City) as Citys from TestTable where DT between '2012-03-01 00:00:00' and '2012-03-31 23:59:59' group by State,City with rollup) q 至于同一字段清除重复内容的功能,一般的报表设计都有此功能。
from t group by 发货单位
order by min(日期时间)
报表里面页脚用SUM
from TestTable
where DT between '2012-03-01 00:00:00' and '2012-03-31 23:59:59'
group by State,City with rollup当States为1时表示片区合计,Citys为1时表示单位合计。以上可以满足你的要求。100分吧。
但是请问,我怎么在片区合计前加上片区字段的内容呢?我怎么也加不上如下请问在FastReport应该如何设计? W1 W2 W3 W4
SUM(W1) SUM(W2) SUM(W3) SUM(W4)
.....
片区名称(这里怎么也加不上) W1(片区合计)W2 W3 W4
.......
总合计(这个字符也加不上) W1(总合计)......
from sysdb
where gbsj between '2012-03-01 00:00:00' and '2012-03-31 23:59:59'
group by pianqu,fahuodanwei with rollu
我得到的报表如下但我想改成如图所示应该怎么改?
p
片区编码固定长度4位:例如从A000开始
发货单位编码:片区+任意字符,例如是A000xxxx下面d是日期,a1是单位,a2片区,q1是重量(其它不列出), left(a1,4)中4表示片区编码的长度select a1,q1 from
(select min(d) as d, a1,sum(q1) as q1 from t group by a1
union all
select max(d) as d, a2,sum(q1) as q1 from t group by a2
union all
select getdate()+2 as d,'ZZZZZZZZ' as a3,sum(q1) q1 from t) as X
order by left(a1,4),d
union all
select zone as dw,sum(weight1),sum(weight2),sum(weight3),sum(cs) from table where time between time1 and time2 group by zonedw--单位
zone--片区
难道我理解错了?
case when Citys=1 then N'合计' else City end as Citys,
W1,W2,W3,Nums
from (
select State,City,sum(W1) as W1,sum(W2) as W2,sum(W3) as W3,sum(Nums) as Nums ,Grouping(State) as States,Grouping(City) as Citys
from TestTable
where DT between '2012-03-01 00:00:00' and '2012-03-31 23:59:59'
group by State,City with rollup) q
至于同一字段清除重复内容的功能,一般的报表设计都有此功能。