数据库SQL SERVER 2000,某表字段:
单号 客户编号 产品编号 产品单价 出库数量 制单时间 出货地点 .......
其中产品数量分正负类型,负表示进库(退货、换货之类),正表示出库;有数量有单价表示进出库的数量,有数量无单价的表示为赠品数量
有一报表需输出这样的效果:
根据制单时间、出货地点生成:
客户编号 搭赠数量 出货数量 出货金额 退货数量 退货金额 净销售量 净销售额
001 0 10 500.00 -1 -50.00 9 450.00
002 2 20 300.00 0 0.00 20 300.00
003 0 30 200.00 0 0.00 30 200.00
单号 客户编号 产品编号 产品单价 出库数量 制单时间 出货地点 .......
其中产品数量分正负类型,负表示进库(退货、换货之类),正表示出库;有数量有单价表示进出库的数量,有数量无单价的表示为赠品数量
有一报表需输出这样的效果:
根据制单时间、出货地点生成:
客户编号 搭赠数量 出货数量 出货金额 退货数量 退货金额 净销售量 净销售额
001 0 10 500.00 -1 -50.00 9 450.00
002 2 20 300.00 0 0.00 20 300.00
003 0 30 200.00 0 0.00 30 200.00
cusno prodno price outqty madetime outaddrSELECT isnull(send.cusno,isnull(out.cusno,in.cusno)) as cusno,isnull(send.sendqty,0) as sendqty,isnull(out.qty,0) as outqty,isnull(out.outmoney,0) as outmoney,isnull(in.inqty,0) as inqty,isnull(in.inmoney,0) as inmoney,isnull(out.outqty,0)-isnull(in.inqty,0) as saleqty,isnull(out.outmoney,0)-isnull(in.inmoney,0) as salemoney
FROM
(select cusno,sum(outqty) as sendqty where price is null group by cusno) as send full join
(select cusno,sum(outqty) as outqty,sum(outqty*price) as outmoney where outqty>0 and (not price is null) group by cusno) as out on send.cusno=out.cusno full join
(select cusno,sum(outqty) as inqty,sum(outqty*price) as inmoney where outqty<0 and (not price is null) group by cusno) as in on isnull(send.cusno,out.cusno)=in.cusno
SELECT isnull(two.cusno,in.cusno) as cusno,isnull(two.sendqty,0) as sendqty,isnull(two.outqty,0) as outqty,isnull(two.outmoney,0) as outmoney,isnull(in.inqty,0) as inqty,isnull(in.inmoney,0) as inmoney,isnull(two.outqty,0)-isnull(in.inqty,0) as saleqty,isnull(two.outmoney,0)-isnull(in.inmoney,0) as salemoney
FROM
(select isnull(send.cusno,out.cusno) as cusno,send.sendqty,out.outqty,out.outmoney from (select cusno,sum(outqty) as sendqty where price is null group by cusno) as send full join
(select cusno,sum(outqty) as outqty,sum(outqty*price) as outmoney where outqty>0 and (not price is null) group by cusno) as out on send.cusno=out.cusno) as two full join
(select cusno,sum(outqty) as inqty,sum(outqty*price) as inmoney where outqty<0 and (not price is null) group by cusno) as in on two.cusno=in.cusno
如何动态地根据条件查询?
例如:查询datetimepicker1到datetimepicker2选择的时间段内、客户编号从edit1到edit2、库位号为combobox的记录
select fields_list (这里掉了from你的那个表) where …… group by ……真是粗心!呵呵。