怎样才能在两个表获得要求得数据
小弟是在(用CBC 5)编一个进销存得单机版的软件在理利用创建一个函数实现在进货表中提取数据到库存表时发现一个问题:
struct mySEL {
AnsiString PRUNO;
int TOT_STK;//总进货
int STK_PIN;//进货件数
int STK_PBK;//退货件数
int STK_PCS;//库存件数
int STK_SAL;//销售件数
int STK_PNT;//进货订货件数
int STK_PONT;//销售订货件数
};
DynamicArray<mySEL>mySELArray;
//
void __declspec(dllexport) SqlSumSelNo(TQuery *Query,AnsiString database,bool T)
{
Query->DatabaseName=database;
Query->Close();
Query->SQL->Clear();
//
Query->FieldDefs->Clear();
Query->FieldDefs->Add("PRUNO",ftString,16,false);//货号
Query->FieldDefs->Add("SAL",ftInteger,0,false); //销售件数
Query->FieldDefs->Add("STK",ftInteger,0,false); //总进货
Query->FieldDefs->Add("PIN",ftInteger,0,false); //进货件数
Query->FieldDefs->Add("PBK",ftInteger,0,false); //退货件数
Query->FieldDefs->Add("PCS",ftInteger,0,false); //库存件数
Query->FieldDefs->Add("PNT",ftInteger,0,false); //进货订货件数
Query->FieldDefs->Add("PONT",ftInteger,0,false); //销售订货件数
//
if(T){
Query->SQL->Add("select distinct A.PRUNO as PRUNO,"
"sum(A.PCS) as SAl,"
"B.TOT_STK-sum(A.PCS) as PCS");
Query->SQL->Add("from DREPT as A,mSTOCK as B");
Query->SQL->Add("where A.PRUNO=B.PRUNO");
Query->SQL->Add("group by PRUNO");
}
else
{Query->SQL->Add("select distinct A.PRUNO as PRUNO,"
"sum(A.PCS)+sum(B.STK_PNT) as PNT");
Query->SQL->Add("from DPENT as A,mSTOCK as B");
Query->SQL->Add("where B.PRUNO=A.PRUNO");
Query->SQL->Add("group by PRUNO");
}
Query->Open();
//
mySELArray.Length=Query->RecordCount;
if(!Query->Eof) {
for(int i=0;i<Query->RecordCount;i++){
Query->First();
if(T){
mySELArray[i].STK_SAL=Query->FieldByName("SAL")->AsInteger;
mySELArray[i].STK_PCS=Query->FieldByName("PCS")->AsInteger;
mySELArray[i].PRUNO=Query->FieldByName("PRUNO")->AsString;
}
else
{mySELArray[i].STK_PONT=Query->FieldByName("PNT")->AsInteger;
mySELArray[i].PRUNO=Query->FieldByName("PRUNO")->AsString;
}
Query->Next();
}
}
//
TTable *DB=DBM->mSTK;
for(int i=0;i<mySELArray.Length;i++){
/*Query->Close();
Query->SQL->Clear();
Query->SQL->Add("Update mSTOCK.DBF");
Query->SQL->Add("set");
Query->SQL->Add("STK_SAL='"+mySELArray[i].SELNO+"'");
Query->SQL->Add("where PRUNO='"+mySELArray[i].PRUNO+"'");
Query->Open();*/
DB->First();
while (!DB->Eof&&DB->FieldValues["PRUNO"]==mySELArray[i].PRUNO){
if(DB->State!=dsEdit) DB->Edit();
if(T){
DB->FieldValues["STK_SAL"]=mySELArray[i].STK_SAL;
DB->FieldValues["STK_PCS"]=mySELArray[i].STK_PCS;
}
else
{
DB->FieldValues["STK_PONT"]=mySELArray[i].STK_PONT;
}
DB->Next();
}
}
DB->Close();