如何用SQL语句实现将一个表中的某些字段的求和值赋给变量？(100分)

I

income

Unregistered / Unconfirmed

GUEST, unregistred user!

• 1999-09-27

• #1

如何用SQL语句实现将一个表中的某些字段的求和值赋给变量？这样写对吗？
select sum(column) into : where 条件

 

W

wuyi

Unregistered / Unconfirmed

GUEST, unregistred user!

• 1999-09-27

• #2

query1.sql.add('select sum(column) as varname from table1 where ')
query1.open;
prgval := query1.fieldvalues['varname'];

 

J

Jetideas

Unregistered / Unconfirmed

GUEST, unregistred user!

• 1999-09-27

• #3

Query1.Close;
Query1.Clear;
Query1.Add('select 'varname'=sum(column) form table1');
Query1.Open;
ExtendVar:=Query.FieldByName('varname').AsInteger;

 

P

pxlei

Unregistered / Unconfirmed

GUEST, unregistred user!

• 1999-09-27

• #4

Query1.Close;
Query1.Clear;
Query1.Add('select sum(column) as psum form table1');
Query1.Open;
ppsum:=query1.fieldbyname('psum').asfloat;
另:
Delphi的Sql中不支持select * into 语句.要么用:
insert into where ...

 

S

shenjian

Unregistered / Unconfirmed

GUEST, unregistred user!

• 1999-09-27

• #5

实现的方法很多！
wuyi的方法安全可靠除了解决统计求和外还可解决利用游标进行SQL查询传出数据的问题！明确期间:
Query1.Clear;
query1.sql.add('select sum(column) as Biao from table1 where ')
Query1.open;
MyVal := query1.fieldvalues['Biao'];
多变量时不同;

 

C

chen_andy

Unregistered / Unconfirmed

GUEST, unregistred user!

• 1999-09-27

• #6

declare @max :datatype

select sunm(field) into @max
from tablename
where .....
编写存储过程，用sql调用

 

C

churchill

Unregistered / Unconfirmed

GUEST, unregistred user!

• 1999-09-27

• #7

哎!慢了.

 

C

cAkk

Unregistered / Unconfirmed

GUEST, unregistred user!

• 1999-09-27

• #8

呵呵,看来income是从pb过来的, 我也觉得pb这点比较方便.

 

I

income

Unregistered / Unconfirmed

GUEST, unregistred user!

• 1999-09-28

• #9

还没完，我说的情况还有多个字段，我用下列语句总是报错。
query1.close;
query1.sql.clear;
query1.sql.add('select sum(col1),sum(col2) asr1,var2 from table');
query1.open;

 

D

DJ.

Unregistered / Unconfirmed

GUEST, unregistred user!

• 1999-09-28

• #10

>query1.sql.add('select sum(col1),sum(col2) asr1,var2 from table');
我猜你应该这样写：
query1.sql.add('select sum(col1) as var1,sum(col2) as var2 from table');

 

C

cAkk

Unregistered / Unconfirmed

GUEST, unregistred user!

• 1999-09-28

• #11

如果asr1,var2 是字段名,就不能这样用,因为如果sql语句中有类似sum,max的函数,
就只能返回一行记录.

 

C

cytown

Unregistered / Unconfirmed

GUEST, unregistred user!

• 1999-09-28

• #12

declare @sum_all integer
select @sum_all=sum(column) from table1
要得到@sum_all, 需要这样:
select sum_all=@sum_all
然后可以query1.fieldbyname('sum_all').asinteger...
不过我想你要在stored procedure中使用@sum_all才有必要这么做

 

X

X

Unregistered / Unconfirmed

GUEST, unregistred user!

• 1999-09-28

• #13

你错误的原因是没有加 Group by ...........

 

I

income

Unregistered / Unconfirmed

GUEST, unregistred user!

• 1999-09-29

• #14

多人接受答案了。