//计算采购情况
SQLStr := 'insert into T_Temp(CoutName,InNum,InMoney) select a.CoutName,'+
'b.TNumber as InNum,b.Price*b.TNumber as TInMoney '+
'from T_Bill a,T_BillDetail b '+
'where a.BillCode=b.BillCode '+
' and a.Identifier='+''''+'采购进货'+''''+
'and addDate between :StartDate and :EndDate'; with frmDM.qryAll do
begin
Close;
SQL.Clear;
SQL.Add(SQLStr);
Parameters.ParamByName('StartDate').Value:=edtBegin.Value;
Parameters.ParamByName('EndDate').Value:=edtEnd.Value;
ExecSQL;
end; SQLStr := 'insert into T_Temp(CoutName,OutNum,OutMoney) select CoutName,'+
'b.TNumber as OutNum,b.Price*b.TNumber as OutMoney '+
'from T_Bill a,T_BillDetail b '+
'where a.BillCode=b.BillCode '+
' and a.Identifier='+''''+'采购退货'+''''+
'and addDate between :StartDate and :EndDate '; with frmDM.qryAll do
begin
Close;
SQL.Clear;
SQL.Add(SQLStr);
Parameters.ParamByName('StartDate').Value:=edtBegin.Value;
Parameters.ParamByName('EndDate').Value:=edtEnd.Value;
ExecSQL;
end; SQLStr := 'select CoutName,sum(InNum) as TInNumber,sum(InMoney) as TInMoney,'+
'sum(OutNum) as TOutNumber,sum(OutMoney) as TOutMoney,'+
'sum(InNum) - sum(OutNum) as THjNumber,'+
'sum(InMoney) - sum(OutMoney) as THjMoney '+
'from T_Temp group by CoutName'; with qryCgGysGhHz do
begin
Close;
SQL.Clear;
SQL.Add(SQLStr);
Open;
end;
select *,TInNumber-TOutNumber as THjNumber,TInMoney-TOutMoney as THjMoney from(
select a.CountName,
sum(case when a.Identifier='采购进货' then b.TNumber else 0 end) as TInNumber,
sum(case when a.Identifier='采购进货' then b.Price*b.TNumber else 0 end) as TInMoney,
sum(case when a.Identifier='采购退货' then b.TNumber else 0 end) as TOutNumber,
sum(case when a.Identifier='采购退货' then b.Price*b.TNumber else 0 end) as TOutMoney
from T_Bill a inner join T_BillDetail b on a.BillCode=b.BillCode
where addDate between :StartDate and :EndDate
group by a.CountName
)t
SQLStr := 'select *,TInNumber-TOutNumber as THjNumber,TInMoney-TOutMoney as THjMoney from('+
'select a.CountName,'+
'sum(case when a.Identifier='+QuotedStr('采购进货')+' then b.TNumber else 0 end) as TInNumber,'+
'sum(case when a.Identifier='+QuotedStr('采购进货')+' then b.Price*b.TNumber else 0 end) as TInMoney,'+
'sum(case when a.Identifier='+QuotedStr('采购退货')+' then b.TNumber else 0 end) as TOutNumber,'+
'sum(case when a.Identifier='+QuotedStr('采购退货')+' then b.Price*b.TNumber else 0 end) as TOutMoney '+
'from T_Bill a inner join T_BillDetail b on a.BillCode=b.BillCode '+
'where addDate between :StartDate and :EndDate '+
'group by a.CountName '+
')t'; with qryCgGysGhHz do
begin
Close;
SQL.Clear;
SQL.Add(SQLStr);
Open;
end;提示错误:OLE error 800A0E7C 我的数据库是Access
楼下帮帮他!
select *,TInNumber-TOutNumber as THjNumber,TInMoney-TOutMoney as THjMoney from(
select a.CountName,
sum(iif a.Identifier='采购进货',b.TNumber,0) as TInNumber,
sum(iif a.Identifier='采购进货',b.Price*b.TNumber,0) as TInMoney,
sum(iif a.Identifier='采购退货',b.TNumber,0) as TOutNumber,
sum(iif a.Identifier='采购退货',b.Price*b.TNumber,0) as TOutMoney
from T_Bill a inner join T_BillDetail b on a.BillCode=b.BillCode
where addDate between :StartDate and :EndDate
group by a.CountName
)t
SQLStr := 'select *,TInNumber-TOutNumber as THjNumber,TInMoney-TOutMoney as THjMoney from('+
'select a.CountName,'+
'sum(iif a.Identifier=''采购进货'',b.TNumber,0) as TInNumber,'+
'sum(iif a.Identifier=''采购进货'',b.Price*b.TNumber,0) as TInMoney,'+
'sum(iif a.Identifier=''采购退货'',b.TNumber,0) as TOutNumber,'+
'sum(iif a.Identifier=''采购退货'',b.Price*b.TNumber,0) as TOutMoney'+
'from T_Bill a inner join T_BillDetail b on a.BillCode=b.BillCode'+
'where addDate between :StartDate and :EndDate'+
'group by a.CountName'+
')t'
'select a.CountName,'+
'sum(iif a.Identifier='+QuotedStr('采购进货')+',b.TNumber,0) as TInNumber,'+
'sum(iif a.Identifier='+QuotedStr('采购进货')+',b.Price*b.TNumber,0) as TInMoney,'+
'sum(iif a.Identifier='+QuotedStr('采购退货')+',b.TNumber,0) as TOutNumber,'+
'sum(iif a.Identifier='+QuotedStr('采购退货')+',b.Price*b.TNumber,0) as TOutMoney '+
'from T_Bill a inner join T_BillDetail b on a.BillCode=b.BillCode '+
'where addDate between :StartDate and :EndDate '+
'group by a.CountName '+
')t '; with qryCgGysGhHz do
begin
Close;
SQL.Clear;
SQL.Add(SQLStr);
Open;
end;
还是一样的错误。
SQLStr := 'select *,TInNumber-TOutNumber as THjNumber,TInMoney-TOutMoney as THjMoney from('+
'select a.CoutName,'+
'sum(iif( a.Identifier=''采购进货'',b.TNumber,0)) as TInNumber,'+
'sum(iif( a.Identifier=''采购进货'',b.Price*b.TNumber,0)) as TInMoney,'+
'sum(iif( a.Identifier=''采购退货'',b.TNumber,0)) as TOutNumber,'+
'sum(iif( a.Identifier=''采购退货'',b.Price*b.TNumber,0)) as TOutMoney '+
'from T_Bill a inner join T_BillDetail b on a.BillCode=b.BillCode '+
'where a.Identifier=''采购进货'' or a.Identifier=''采购退货'' '+
'and addDate between :StartDate and :EndDate '+
'group by a.CoutName'+
')t'; with qryCgGysGhHz do
begin
Close;
SQL.Clear;
SQL.Add(SQLStr);
Parameters.ParamByName('StartDate').Value:=edtBegin.Value;
Parameters.ParamByName('EndDate').Value:=edtEnd.Value;
Open;
end;
最终测试通过的算法!太感谢大家了!
没必要.
因为在前面已经用了 iif 了.