查询报表用数据值做栏位【Delphi】

我们用Delphi做数据库应用开发时，常常会要求用栏位里的某个值变成栏位来统计查询，比如查询某物料在某一年内各个月份发生的数量小计；再如查询某个时期内，每款产品的销售数量；再如查询某个月份各个部门的开支合计，等等。

以下是Delphi + msSQL 的实例代码。

procedure TfmsoPackQty.nnEnqCompareClick(Sender: TObject);
var
 sSo:String;
 swhere,sLabel:String;
 i,j:Integer;
begin
  inherited;
  Screen.Cursor:=crSqlWait;
  //建临时表和初始数据
  sSo:=qMaster.fieldbyname('sono').AsString;
  sSql:='if object_id(N''[tempdb]..[#_jhERPsoPack]'') is not null drop table [#_jhERPsoPack] ';
  sSql:=sSql+'Create table [#_jhERPsoPack]([itno][varchar](15),[cono][varchar](7),'
        +'[Qty][int],[pkQty][int]) ';
  sSql:=sSql+'Insert into [#_jhERPsoPack](itno,cono,Qty) Select itno,cono,qty '
        +'from Salesub where sono='+''''+sSo+'''';
  qry5.Close;
  qry5.SQL.Text:=sSql;
  qry5.ExecSQL;
  //填累计分割数
  sSql:='update d1 set d1.pkQty=d2.Qtys from [#_jhERPsoPack] d1,('
        +'select itno,cono,sum(Qty)as Qtys from soPackLine where sono='+''''+sSo+''''
        +' group by itno,cono)d2 where d1.itno=d2.itno and d1.cono=d2.cono ';
  qry5.Close;
  qry5.SQL.Text:=sSql;
  qry5.ExecSQL;
  //装配查询语句
  qry5.Close;
  qry5.SQL.Text:='select max(AreaID)as its from soPackLine where sono='+''''+sSo+'''';
  qry5.Open;
  j:=qry5.fieldbyname('its').AsInteger;
  sSql:='Select d1.*,d1.Qty-d1.pkQty as QtyDiff';
  for i:=0 to j do
  begin
    sLabel:='第'+intToStr(i)+'次';
    sWhere:=' where d2.itno=d1.itno and d2.cono=d1.cono and d2.AreaID='+intToStr(i);
    sSql:=sSql+',(select d2.Qty from soPackLine d2'+sWhere+')as '+''''+sLabel+'''';
  end;
  sSql:=sSql+' from  [#_jhERPsoPack] d1 ';
  Dm1.Q1look.Close;
  Dm1.Q1look.SQL.Text:=sSql;
  Dm1.Q1look.Open;
  if fmAlook=nil then fmAlook:=TfmAlook.create(application)else fmAlook.Show;
  fmAlook.Lbl1.Caption:='订单走货分割数量与订单数量对比';
  fmAlook.NN3.Enabled:=true;
 with fmAlook.DBGridEh1 do
 begin
  Columns[0].Title.caption:='物料编号';
  Columns[1].Title.caption:='色号';
  Columns[2].Title.caption:='订单数量';
  Columns[3].Title.caption:='累计分割';
  Columns[4].Title.caption:='订单-分割';
  Columns[0].Footer.valuetype:=fvtStatictext;
  Columns[0].Footer.value:='合计';
  for i:=2 to j+5 do
  begin
    Columns[i].Footer.valuetype:=fvtSum;
    Columns[i].Footer.fieldName:=Columns[i].FieldName;
  end;
 end;
 dm1.GridReWidth(fmAlook.DBGridEh1);
 Screen.Cursor:=crDefault;
end;