??,不太明白你的意思 如果是为了求所有数量和金额的和,为什么还要group by SELECT SUM(数量),SUM(金额) FROM XS 难道不行吗?
做个VIEW先实现求同一商品代码的数量与金额之和,再对VIEW求和
你用什么数据库? 如果是ACCESS, 你只能做两次查询了,或者对第一次查询的结果做累加 如果是SQL SERVER 或oracle,哪可以做VIEW
是这样吗?可我还是不行 procedure TForm1.Button1Click(Sender: TObject); var a,b:integer; begin with query1 do begin close; with sql do begin clear; add('select 商品代码,sum(数量),sum(金额) from xs group by 商品代码'); query1.open; begin with query2 do begin close; with sql do begin clear; add('select sum(数量),sum(金额) from xs '); query2.open; a:=query2.fieldbyname('sum of 金额').value; b:=query2.fieldbyname('sum of 数量').value; end; end; end; end; end; end.
a := query2.Fields[0].Value; b := query2.Fields[1].Value;
是不是可以这样 procedure TForm1.Button1Click(Sender: TObject); var a,b:integer;
begin with query1 do begin close; with sql do begin clear; add('select 商品代码,sum(数量),sum(金额) from xs group by 商品代码'); query1.open; query1.frist; a:=0; b:=0; while not quer1.empty do begin a:=a+query2.fieldbyname('sum(金额)').value; b:=b+query2.fieldbyname('sum(数量)').value; query1.next; end; end; end; end;
//对每个商品的合计 query1.Close; query1.SQL.Text := 'SELECT 商品代码,SUM(数量),SUM(金额) FROM xs GROUP BY 商品代码'; query1.Open; //...你要做的处理 query1.Close; //对所以商品的合计 query2.Close; query2.SQL.Text := 'SELECT SUM(数量),SUM(金额) FROM xs'; query2.Open; a := query2.Fields[0].Value; b := query2.Fields[1].Value; query2.Close;
按道理讲你上面的应该是可以的啊为什么不行啊? 可以把报错的信息告诉我吗? 我试过你的程序,可以通过的啊! 我把源码给你试下吧:unit Unit1;interfaceuses Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs, Grids, DBGrids, Db, DBTables, StdCtrls;type TForm1 = class(TForm) Query1: TQuery; DataSource1: TDataSource; DBGrid1: TDBGrid; Database1: TDatabase; Button1: TButton; Edit1: TEdit; Edit2: TEdit; Edit3: TEdit; Edit4: TEdit; procedure Button1Click(Sender: TObject); private { Private declarations } public { Public declarations } end;var Form1: TForm1;implementation{$R *.DFM}procedure TForm1.Button1Click(Sender: TObject); var a,b:integer; c,d:integer; begin //求出按同一商品代码的数量与金额之和 with query1 do begin close; sql.clear; sql.Add('select id,sum(num) as 数量,sum(Money) as 金额 from test group by id'); Open; if recordcount <> 0 then a:=fieldbyname('数量').Asinteger; b:=fieldbyName('金额').Asinteger; end; EDIT1.text:= inttostr(a); edit2.Text :=inttostr(b); ////////////////////////////////////////////// //取得所有商品的代码和数量之和 with query1 do begin close; sql.clear; sql.Add('select sum(num) as 数量,sum(Money) as 金额 from test'); Open; if recordcount <> 0 then d:=fieldbyname('金额').Asinteger; c:=fieldbyName('数量').Asinteger; end; EDIT3.text:= inttostr(c); edit4.Text :=inttostr(d);end;end.
如果是为了求所有数量和金额的和,为什么还要group by
SELECT SUM(数量),SUM(金额) FROM XS
难道不行吗?
如果是ACCESS, 你只能做两次查询了,或者对第一次查询的结果做累加
如果是SQL SERVER 或oracle,哪可以做VIEW
procedure TForm1.Button1Click(Sender: TObject);
var a,b:integer;
begin
with query1 do
begin
close;
with sql do
begin
clear;
add('select 商品代码,sum(数量),sum(金额) from xs group by 商品代码');
query1.open;
begin
with query2 do
begin
close;
with sql do
begin
clear;
add('select sum(数量),sum(金额) from xs ');
query2.open;
a:=query2.fieldbyname('sum of 金额').value;
b:=query2.fieldbyname('sum of 数量').value;
end;
end;
end;
end;
end;
end.
b := query2.Fields[1].Value;
procedure TForm1.Button1Click(Sender: TObject);
var
a,b:integer;
begin
with query1 do
begin
close;
with sql do
begin
clear;
add('select 商品代码,sum(数量),sum(金额) from xs group by 商品代码');
query1.open;
query1.frist;
a:=0;
b:=0;
while not quer1.empty do
begin
a:=a+query2.fieldbyname('sum(金额)').value;
b:=b+query2.fieldbyname('sum(数量)').value;
query1.next;
end;
end;
end;
end;
query1.Close;
query1.SQL.Text := 'SELECT 商品代码,SUM(数量),SUM(金额) FROM xs GROUP BY 商品代码';
query1.Open;
//...你要做的处理
query1.Close;
//对所以商品的合计
query2.Close;
query2.SQL.Text := 'SELECT SUM(数量),SUM(金额) FROM xs';
query2.Open;
a := query2.Fields[0].Value;
b := query2.Fields[1].Value;
query2.Close;
可以把报错的信息告诉我吗?
我试过你的程序,可以通过的啊!
我把源码给你试下吧:unit Unit1;interfaceuses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
Grids, DBGrids, Db, DBTables, StdCtrls;type
TForm1 = class(TForm)
Query1: TQuery;
DataSource1: TDataSource;
DBGrid1: TDBGrid;
Database1: TDatabase;
Button1: TButton;
Edit1: TEdit;
Edit2: TEdit;
Edit3: TEdit;
Edit4: TEdit;
procedure Button1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;var
Form1: TForm1;implementation{$R *.DFM}procedure TForm1.Button1Click(Sender: TObject);
var
a,b:integer;
c,d:integer;
begin
//求出按同一商品代码的数量与金额之和
with query1 do
begin
close;
sql.clear;
sql.Add('select id,sum(num) as 数量,sum(Money) as 金额 from test group by id');
Open;
if recordcount <> 0 then
a:=fieldbyname('数量').Asinteger;
b:=fieldbyName('金额').Asinteger;
end;
EDIT1.text:= inttostr(a);
edit2.Text :=inttostr(b);
//////////////////////////////////////////////
//取得所有商品的代码和数量之和
with query1 do
begin
close;
sql.clear;
sql.Add('select sum(num) as 数量,sum(Money) as 金额 from test');
Open;
if recordcount <> 0 then
d:=fieldbyname('金额').Asinteger;
c:=fieldbyName('数量').Asinteger;
end;
EDIT3.text:= inttostr(c);
edit4.Text :=inttostr(d);end;end.
TEST 表示你的 xs
我忘了改回来了;SORRY!