表1 期初表 字段: 业务员, 客户,所属月份,期初余额,期初未开票余额
表2 销售表 字段:业务员,客户,所属月份,销售额
表3 开票表 字段:业务员,客户,所属月份,开票金额
表4 收款表 字段:业务员,客户,所属月份,收款金额
要求查询按特定的所属月份来查,结果如下
业务员 sum(期初余额) sum(销售额) sum(收款金额) 期末余额 sum(期初未开票余额) sum(开票金额) 期末未开票余额
其中 期末余额和期末未开票余额是计算字段 期末余额=期初余额+销售额-收款金额
期末未开票余额=期初未开票余额+销售额-开票金额4张表每一条记录均不能少SQL语句怎么写,谢谢!
深夜求助!
表2 销售表 字段:业务员,客户,所属月份,销售额
表3 开票表 字段:业务员,客户,所属月份,开票金额
表4 收款表 字段:业务员,客户,所属月份,收款金额
要求查询按特定的所属月份来查,结果如下
业务员 sum(期初余额) sum(销售额) sum(收款金额) 期末余额 sum(期初未开票余额) sum(开票金额) 期末未开票余额
其中 期末余额和期末未开票余额是计算字段 期末余额=期初余额+销售额-收款金额
期末未开票余额=期初未开票余额+销售额-开票金额4张表每一条记录均不能少SQL语句怎么写,谢谢!
深夜求助!
解决方案 »
- activex里访问webservices问题
- 一个关于StringGrid的问题,希望大家能多多帮助,谢谢哦!!
- 如何拦截WINDOWS的消息??
- Delphi如何连接SQL server?
- 回答必有高分!帮兄弟一下!
- Delphi 500人的QQ群开放了,高级群号 46722653
- 如何在WIN2K下关闭或重新启动计算机???
- 一个关于数据库统计的问题.
- 用SQL语句中的ORDER BY如何排序两个字段的联合结果?
- 现有ForPro的记录8000条,要将它们转换成Paradox或MS Access的记录,要怎么转换?请给出详细的步骤。
- delphi程序在win8.1下无法打开
- delphi7中使用代码选中TDBGid控件中的一行数据
select 业务员, 所属月份, sum(期初余额) as 期初余额, sum(销售额) as 销售额, sum(收款金额) as 收款金额,
sum(期初余额) + sum(销售额) + sum(收款金额) as 期末余额,
sum(期初未开票余额) as 期初未开票余额, sum(开票金额) as 开票金额,
sum(期初未开票余额) + sum(销售额) - sum(开票金额) as 期末未开票余额
from
(
select 业务员, 所属月份, 期初余额, 0 as 销售额, 0 as 收款金额, 期初未开票余额, 0 as 开票金额, 0 as 期末未开票余额
from 期初表
union all
select 业务员, 所属月份, 0 as 期初余额, 销售额, 0 as 收款金额, 0 as 期初未开票余额, 0 as 开票金额, 0 as 期末未开票余额
from 销售表
union all
select 业务员, 所属月份, 0 as 期初余额, 0 as 销售额, 0 as 收款金额, 0 as 期初未开票余额, 开票金额, 0 as 期末未开票余额
from 开票表
union all
select 业务员, 所属月份, 0 as 期初余额, 0 as 销售额, 收款金额, 0 as 期初未开票余额, 0 as 开票金额, 0 as 期末未开票余额
from 收款表
) Org
group by 业务员, 所属月份
' sum(qcye)+sum(ysje)-sum(je) as qmye, '+
' sum(qcfpje) as qcfp, sum(jshj) as fpje,'+
' sum(qcfpje)+sum(ysje)-sum(jshj) as qmfpye '+
' from '+
'( '+
' select ywy,qc,0 as xsje,0 as skje,0 as qmye,qcfp,0 as fpje,0 as qmfpye '+
' from yskqcye '+
' union all '+
' select ywy,0 as qc,xsje,0 as skje,0 as qmye,qcfp,0 as fpje,0 as qmfpye '+
' from xsb '+
' union all '+
' select ywy,0 as qc,0 as xsje,skje,0 as qmye,qcfp,0 as fpje,0 as qmfpye '+
' from skb '+
')org '+
' where cplx='''+comcplx.Text+''' and ssyf='''+comssyf.Text+''''+
' group by ywy '); 编译后出错
Invalid use of keyword
Token :select
Line Numbe:1
你可以先测试中间那段
select ywy,qc,0 as xsje,0 as skje,0 as qmye,qcfp,0 as fpje,0 as qmfpye
from yskqcye
union all
select ywy,0 as qc,xsje,0 as skje,0 as qmye,qcfp,0 as fpje,0 as qmfpye
from xsb
union all
select ywy,0 as qc,0 as xsje,skje,0 as qmye,qcfp,0 as fpje,0 as qmfpye
from skb
另外不建议 ' where cplx='''+comcplx.Text+''' and ssyf='''+comssyf.Text+''''这种写法。用Parameters
找不出原因procedure TForm1.comCplxChange(Sender: TObject);
begin
if comHzfs.Text='按业务员汇总' then
begin
with data.xshz do
begin
close;
Sql.Clear;
SQL.Add('select ywy,sum(qcye) as qc ,sum(ysje) as xsje,sum(je) as skje, '+
' sum(qcye)+sum(ysje)-sum(je) as qmye, '+
' sum(qcfpje) as qcfp, sum(jshj) as fpje,'+
' sum(qcfpje)+sum(ysje)-sum(jshj) as qmfpye '+
' from( '+
' select ywy,qc,0 as xsje,0 as skje,0 as qmye,qcfp,0 as fpje,0 as qmfpye '+
' from yskqcye'+
' union all '+
' select ywy,0 as qc,xsje,0 as skje,0 as qmye,0 as qcfp,0 as fpje,0 as qmfpye '+
' from xsb '+
' union all '+
' select ywy,0 as qc,0 as xsje,skje,0 as qmye,0 as qcfp,0 as fpje,0 as qmfpye '+
' from skb '+
' union all '+
' select ywy,0 as qc,0 as xsje,0 as skje,0 as qmye,0 as qcfp,fpje,0 as qmfpye '+
' from xxb '+
') org '+
' where cplx=:cplx and ssyf=:ssyf '+
' group by ywy ');
ParamByName('cplx').AsString:=comcplx.Text;
ParamByName('ssyf').AsString:=comssyf.Text;
open;
end;
end;end.
难道paradox数据库不一样的?
那你把中间那段创建一个临时表吧