select substring(convert(varchar(20),max(b.Fdate),20),0,8) as 会计时间,e.Fname as 业务员,o.Fname as 客户,max(en.fconsignamount) as 销售额,max(new.fsettleamountfor) as 回款额,max(vou.FamountFor) as 佣金
from (((((icstockbillentry as en inner join icstockbill as b on en.Finterid = b.finterid)left join t_organization as o on o.Fitemid = b.fsupplyid) left join t_rp_newreceivebill as new on new.Fcustomer = o.Fitemid)inner join t_emp as e on e.Fitemid = o.Femployee)left join t_itemdetail as it on it.F3 = e.Fitemid and it.F1 = o.Fitemid)left join t_voucherentry as vou on it.Fdetailid = vou.Fdetailid
where (en.fentryselfb0158 > 0 and b.ftrantype = '21' and (b.fheadselfb0146 ='' or b.fheadselfb0146 is null )and b.Frob = 1 and en.Fitemid = '2497' and e.Fstates = '在职' )
or (b.ftrantype = '21' and b.fheadselfb0146 ='销售退回' and b.Frob = -1 and en.Fitemid = '2497' and e.Fstates = '在职') group by o.Fname,e.Fname order by e.fname 结果如下:
现在想要 回款额和佣金的SUM值
select substring(convert(varchar(20),max(b.Fdate),20),0,8) as 会计时间,e.Fname as 业务员,o.Fname as 客户,max(en.fconsignamount) as 销售额,sum(new.fsettleamountfor) as 回款额,max(vou.FamountFor) as 佣金
from (((((icstockbillentry as en inner join icstockbill as b on en.Finterid = b.finterid)left join t_organization as o on o.Fitemid = b.fsupplyid) left join t_rp_newreceivebill as new on new.Fcustomer = o.Fitemid)inner join t_emp as e on e.Fitemid = o.Femployee)left join t_itemdetail as it on it.F3 = e.Fitemid and it.F1 = o.Fitemid)left join t_voucherentry as vou on it.Fdetailid = vou.Fdetailid
where (en.fentryselfb0158 > 0 and b.ftrantype = '21' and (b.fheadselfb0146 ='' or b.fheadselfb0146 is null )and b.Frob = 1 and en.Fitemid = '2497' and e.Fstates = '在职' )
or (b.ftrantype = '21' and b.fheadselfb0146 ='销售退回' and b.Frob = -1 and en.Fitemid = '2497' and e.Fstates = '在职') group by o.Fname,e.Fname order by e.fname 只要这2列SUM 就会对前面销售额2个= 350的值对应列相加一次 急,谢谢各位 。
from (((((icstockbillentry as en inner join icstockbill as b on en.Finterid = b.finterid)left join t_organization as o on o.Fitemid = b.fsupplyid) left join t_rp_newreceivebill as new on new.Fcustomer = o.Fitemid)inner join t_emp as e on e.Fitemid = o.Femployee)left join t_itemdetail as it on it.F3 = e.Fitemid and it.F1 = o.Fitemid)left join t_voucherentry as vou on it.Fdetailid = vou.Fdetailid
where (en.fentryselfb0158 > 0 and b.ftrantype = '21' and (b.fheadselfb0146 ='' or b.fheadselfb0146 is null )and b.Frob = 1 and en.Fitemid = '2497' and e.Fstates = '在职' )
or (b.ftrantype = '21' and b.fheadselfb0146 ='销售退回' and b.Frob = -1 and en.Fitemid = '2497' and e.Fstates = '在职') group by o.Fname,e.Fname order by e.fname 结果如下:
现在想要 回款额和佣金的SUM值
select substring(convert(varchar(20),max(b.Fdate),20),0,8) as 会计时间,e.Fname as 业务员,o.Fname as 客户,max(en.fconsignamount) as 销售额,sum(new.fsettleamountfor) as 回款额,max(vou.FamountFor) as 佣金
from (((((icstockbillentry as en inner join icstockbill as b on en.Finterid = b.finterid)left join t_organization as o on o.Fitemid = b.fsupplyid) left join t_rp_newreceivebill as new on new.Fcustomer = o.Fitemid)inner join t_emp as e on e.Fitemid = o.Femployee)left join t_itemdetail as it on it.F3 = e.Fitemid and it.F1 = o.Fitemid)left join t_voucherentry as vou on it.Fdetailid = vou.Fdetailid
where (en.fentryselfb0158 > 0 and b.ftrantype = '21' and (b.fheadselfb0146 ='' or b.fheadselfb0146 is null )and b.Frob = 1 and en.Fitemid = '2497' and e.Fstates = '在职' )
or (b.ftrantype = '21' and b.fheadselfb0146 ='销售退回' and b.Frob = -1 and en.Fitemid = '2497' and e.Fstates = '在职') group by o.Fname,e.Fname order by e.fname 只要这2列SUM 就会对前面销售额2个= 350的值对应列相加一次 急,谢谢各位 。
350 67282.00 5010.00
350
390 850 560
Sum 以后的值销售额 回款额 佣金350 134564.00 10020.00
350
390 850 560
left join t_rp_newreceivebill as new on new.Fcustomer = o.Fitemid)
inner join t_emp as e on e.Fitemid = o.Femployee)
left join t_itemdetail as it on it.F3 = e.Fitemid and it.F1 = o.Fitemid
where (en.fentryselfb0158 > 0 and b.ftrantype = '21' and (b.fheadselfb0146 ='' or b.fheadselfb0146 is null )and b.Frob = 1 and en.Fitemid = '2497' and e.Fstates = '在职' )
or (b.ftrantype = '21' and b.fheadselfb0146 ='销售退回' and b.Frob = -1 and en.Fitemid = '2497' and e.Fstates = '在职') 这样减了值得还是 那列重复加了一次
如果一样的话,可以统计个数,然后用乘法算啊,然后加上 group by 销售额
一样的销售额 如果算回款额和佣金 就会多加一次。而实际就是一个值。不知道为啥加了2次。
350
390 850 560
结果是这样的只有重复的350的累加了。