----------这样一个查询,如何实现?----------(100分)

B

bobzane

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-06-20

• #1

有这样两个表,想生成如第三个表(或Query),怎样写代码:
表一: 表二:
id date money | id name
10 01-06-15 15 | 10 张三
12 01-06-15 6.5 | 11 李四
11 01-06-15 8 | 12 王五
10 01-06-16 20 | 13 齐六
11 01-06-16 3 ........
.......
想生成表三样子的表: 条件是在01-06-15 到 01-06-16期间
表三:
id money
10 35 //(15+20)
11 12 //(8+3)
12 6.5
........
对某段时间区间内,按个人进行金额统计求和,而生成的表.
怎样实现呢?
请说得详细点.谢谢!!!

 

Z

zwma

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-06-20

• #2

select id,sum(money) from 表一 where date between :rq1 and :rq2

 

W

wind_cloudy

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-06-20

• #3

用SQL SERVER举例
1如果表三存在
insert into 表三(id,money)
select id,sum(money)
from 表一 where date>='2001-06-15' and date<'2001-06-17'
group by id
2如果表三不存在
select id,sum(money)
into 表三
from 表一 where date>='2001-06-15' and date<'2001-06-17'
group by id

 

B

bobzane

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-06-20

• #4

wind_cloudy,
你所打的命令是放在哪里的?可以放到按钮的click事件中吗?
不好意思,我不太理解.我只知道Select命令可以放到query中

 

W

wind_cloudy

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-06-20

• #5

放在query中,用query.execsql执行(别用query.open);

 

B

bobzane

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-06-20

• #6

我用ADOQuery可以这样用吗?
怎么总是出现"未指定的错误"?
我是这样写的:
在ADOQuery1的ConnectionString中指定好数据库(我用的是Access),然后在SQL中这样写:
select ItemNo,sum(money)
from output
where outdate between 01-01-01 and 01-07-01

outdate ItemNo money均是outdate表中的字段.
我也用execsql了
ADOQuery1.execsql;

 

W

wind_cloudy

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-06-20

• #7

要加上group by子句.

 

N

NaCl

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-06-20

• #8

select ItemNo,sum(money)
from output
where outdate between 01-01-01 and 01-07-01 group by ItemNo

 

T

taim

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-06-20

• #9

你应该设置参数，强制转化为日期型。
放在代码中。
with query1 do
close;
sql.add('selece itemno,sum(money) from output where outdata between :riqi1 and :riqi2');
parambyname(riqi1).asdatetime:=strtodate('01-01-01');
parambyname(riqi2).asdatetime:=strtodate('01-07-01');
prepare;
open;
end;

ADOQuery也适用

 

B

bobzane

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-06-20

• #10

真他妈的神了,怎么试都是:
Project aaa.exe raised exception class EOleException with message '未指定的错误'.
Process stopped. Use Step or Run to continue.
只好重新来了.就是找不到错在什么地方

 

S

st_cumt

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-06-20

• #11

with query1 do
close;
sql.clear;
sql.add('selece id,sum(money) as money from output where outdata between :riqi1 and :riqi2');
parambyname(riqi1).asdatetime:=*;//可以是输入，也可以有控件代入
parambyname(riqi2).asdatetime:=*;//
prepare;
open;
end;

 

W

wind_cloudy

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-06-20

• #12

select ItemNo,sum(money)
from output
where outdate between '2001-01-01' and '2001-07-01' //注意加引号
group by ItemNo
另外，如果是select语句,则用query.open;
如果是insert,update,delete等语句用query,execsql;
总之，如果返回结果集用open，不返回用execsql

 

B

bobzane

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-06-21

• #13

前面的意思我明白了,如果在生成的表再加上一个字段,姓名字段,将怎样写SQL呢?
目的表如下:

id name money
10 张三 35 //(15+20)
11 李四 12 //(8+3)
12 王五 6.5
........

 

W

wind_cloudy

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-06-21

• #14

select output.ItemNo,table2.name,sum(money) as money
from output,table2 //table2为题中的表2
where (outdate between '2001-01-01' and '2001-07-01') and
output.id=table2.id
group by output.ItemNo,table2.name

 

B

bobzane

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-06-21

• #15

我是这样写的,看看哪里有问题:
select output1.Itemno,Item.ItemName,sum(money) as output1.money
from output1,Item
where ((Output1.outdate between '01-01-01' and '01-07-01')
and (output1.Itemno=Item.ItemNo))
group by Output1.Itemno,Item.ItemName

 

W

wind_cloudy

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-06-21

• #16

sum(money) as output1.money改为sum(money) as money

 

B

bobzane

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-06-21

• #17

这样也不行,出这样的错误提示:
Error
The SELECT statement includes a reserved word or an argument name that is
misspelled or missing, or the punctuation is incorrect.

ok(按钮)
如果你方便的话,我们用OICQ谈谈怎么样?我的号码是7184908

 

B

bobzane

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-06-21

• #18

我知道怎么做了,不应该有as money,把这个去掉就好了.谢谢!给分了.