我做了一个货品入库单和一个货品出库单,
入库单T_AddBill的字段包括:单号,货品名,采购人,入库时间,价格,入库数量,金额
code name receiver Date price innumber inmoney
出库单T_OutBill的字段包括:单号,货品名,采购人,出库时间,价格,出库数量,金额
code name receiver date price outnumber outmoney
现在我要做一个报表查询,查询货品库存情况:
T_TempNowStorage:单号,货品名,采购人,入库时间,价格,数量, 金额
code name reciever date price storagenum money
其中,数量=入库数量-出库数量,金额=入库金额-出库金额,
请问SQL insert 语句怎么写??
下面是我写的:
procedure TFrm_Data.OpenDataSet(CurrQuery:TAdoQuery;TempSQL:String);
begin
with CurrQuery do
begin
Close;
SQL.Clear;
SQL.Add(TempSQL);
Open;
end;
end;procedure TFrm_Data.ExecDataSet(CurrQuery:TAdoQuery;TempSQL:String);
begin
with CurrQuery do
begin
Close;
SQL.Clear;
SQL.Add(TempSQL);
Execsql;
end;
end;
Frm_Data.ExecDataSet(Frm_Data.Qry_Temp,'delete from T_TempNowStorage');
Frm_Data.ExecDataSet(Frm_Data.Qry_Temp,'insert into T_TempNowStorage (code,name,receiver,Date,price,) select code,name,receiver,Date,price,number,money
from T_Addbill');
Frm_Data.ExecDataSet(Frm_Data.Qry_Temp,'insert into T_TempNowStorag(Name,InNumber,Inmoney,F_Flag) select Name,sum(InNumber),sum(inmoney) as T_Num,T_money,false from T_AddBill group by Name');
Frm_Data.ExecDataSet(Frm_Data.Qry_Temp,'insert into T_TempNowStorage(Name,OutNumber,outmoney,F_Flag) select Name,sum(OutNumber), sum(outmoney),as T_Num,T_outmoney,false from T_OutBill group by Name');
Frm_Data.ExecDataSet(Frm_Data.Qry_Temp,'insert into T_TempNowStorage(Name,InNumber,OutNumber,F_Flag) select Name,Sum(InNumber) as T_In ,Sum(OutNumber) as T_out,true from T_TempNowStorage group by Name');
Frm_Data.ExecDataSet(Frm_Data.Qry_Temp,'delete from T_TempNowStorage where F_flag=false');
Frm_Data.ExecDataSet(Frm_Data.Qry_Temp,'update T_TempNowStorage set StorageNum=InNumber-OutNumber');
Frm_Data.ExecDataSet(Frm_Data.Qry_Temp,'update T_TempNowStorage set Money=Inmoney-Outmoeny');
Frm_Data.OpenDataSet(Qry_NowStorage,'select * from TempNowStorage');
入库单T_AddBill的字段包括:单号,货品名,采购人,入库时间,价格,入库数量,金额
code name receiver Date price innumber inmoney
出库单T_OutBill的字段包括:单号,货品名,采购人,出库时间,价格,出库数量,金额
code name receiver date price outnumber outmoney
现在我要做一个报表查询,查询货品库存情况:
T_TempNowStorage:单号,货品名,采购人,入库时间,价格,数量, 金额
code name reciever date price storagenum money
其中,数量=入库数量-出库数量,金额=入库金额-出库金额,
请问SQL insert 语句怎么写??
下面是我写的:
procedure TFrm_Data.OpenDataSet(CurrQuery:TAdoQuery;TempSQL:String);
begin
with CurrQuery do
begin
Close;
SQL.Clear;
SQL.Add(TempSQL);
Open;
end;
end;procedure TFrm_Data.ExecDataSet(CurrQuery:TAdoQuery;TempSQL:String);
begin
with CurrQuery do
begin
Close;
SQL.Clear;
SQL.Add(TempSQL);
Execsql;
end;
end;
Frm_Data.ExecDataSet(Frm_Data.Qry_Temp,'delete from T_TempNowStorage');
Frm_Data.ExecDataSet(Frm_Data.Qry_Temp,'insert into T_TempNowStorage (code,name,receiver,Date,price,) select code,name,receiver,Date,price,number,money
from T_Addbill');
Frm_Data.ExecDataSet(Frm_Data.Qry_Temp,'insert into T_TempNowStorag(Name,InNumber,Inmoney,F_Flag) select Name,sum(InNumber),sum(inmoney) as T_Num,T_money,false from T_AddBill group by Name');
Frm_Data.ExecDataSet(Frm_Data.Qry_Temp,'insert into T_TempNowStorage(Name,OutNumber,outmoney,F_Flag) select Name,sum(OutNumber), sum(outmoney),as T_Num,T_outmoney,false from T_OutBill group by Name');
Frm_Data.ExecDataSet(Frm_Data.Qry_Temp,'insert into T_TempNowStorage(Name,InNumber,OutNumber,F_Flag) select Name,Sum(InNumber) as T_In ,Sum(OutNumber) as T_out,true from T_TempNowStorage group by Name');
Frm_Data.ExecDataSet(Frm_Data.Qry_Temp,'delete from T_TempNowStorage where F_flag=false');
Frm_Data.ExecDataSet(Frm_Data.Qry_Temp,'update T_TempNowStorage set StorageNum=InNumber-OutNumber');
Frm_Data.ExecDataSet(Frm_Data.Qry_Temp,'update T_TempNowStorage set Money=Inmoney-Outmoeny');
Frm_Data.OpenDataSet(Qry_NowStorage,'select * from TempNowStorage');
FROM
(
SELECT name, SUM(ISNULL(innumber,0)) AS innumber, SUM(ISNULL(inmoney,0)) AS inmoney
FROM T_AddBill
GROUP BY name
) A
LEFT JOIN
(
SELECT name, SUM(ISNULL(outnumber,0)) AS outnumber, SUM(ISNULL(outmoney,0)) AS outmoney
FROM T_OutBill
GROUP BY name
) B
ON A.name=B.name