怎样查询得到(50分)

dohye · 1999-06-05

表A有字段ID和RecQuantity
数据
001 200
001 100
表B有字段ID和SaleQuantity
数据
001 200
001 300
001 200
002 300
怎样查询得到
id totalRec totalsale
001 300 700
002 300

Another_eYes · 1999-06-05

似曾相识???

select b.id as id, sum(a.RecQuantity) as totalRec,
sum(b.SaleQuantity) as totalsale
from a, b where b.id = a.id group by id

menxin · 1999-06-05

不对！没那么简单吧。我觉得至少要用到inner join.

Another_eYes · 1999-06-05

select a.id as id, sum(a.RecQuantity) as totalRec,
sum(b.SaleQuantity) as totalsale
from a union join b group by id

menxin · 1999-06-05

union 语法错误。
union 是用于连接两个select 子句的，你这样用法行吗？

Another_eYes · 1999-06-05

不是union, 是union join, 如果感觉不对那用left outer join或right outer join
吧

CJ · 1999-06-06

LOCAL SQL 不支持的

LSS · 1999-06-06

前面几个我都试过了，不好使，请用下面的，分几步做吧
CREATE TABLE "total.db"
(
id CHAR(10),
totalrec integer,
totalsale integer
)

insert into "total.db" (id,totalRec,totalsale)
SELECT Id, cast(SUM( RecQuantity ) as integer) as totalRec,cast(0 as integer) as totalsale
FROM "a.db" A
GROUP BY Id

insert into "total.db" (id,totalRec,totalsale)
SELECT Id,cast(0 as integer)as totalRec, cast(SUM( SaleQuantity ) as integer) as totalsale
FROM "b.db" B
GROUP BY Id

SELECT id, SUM( totalrec ), SUM( totalsale )
FROM "total.db" Total
GROUP BY id

dohye · 1999-06-06

如果纪录很多的话,会不会使执行的时间变得很长!

dohye · 1999-06-06

另外用inner (left,right) join来连接两个表
也属于叉集连接,得出的数据会比实际的数据多几倍!

SeaSky · 1999-07-21

LSS的办法是行之有效的，时间长短主要在于ID 的种类的多少。
如果纪录很多的话，而ID的值只有十几种，并且a,b 都有关于ID
的索引，total.db中最多纪录数只是ID种类的两倍.追好total.db
在ID 上也加索引。

dohye · 1999-07-25

多人接受答案了。

怎样查询得到(50分)

dohye

Unregistered / Unconfirmed

Another_eYes

Unregistered / Unconfirmed

menxin

Unregistered / Unconfirmed

Another_eYes

Unregistered / Unconfirmed

menxin

Unregistered / Unconfirmed

Another_eYes

Unregistered / Unconfirmed

CJ

Unregistered / Unconfirmed

LSS

Unregistered / Unconfirmed

dohye

Unregistered / Unconfirmed

dohye

Unregistered / Unconfirmed

SeaSky

Unregistered / Unconfirmed

dohye

Unregistered / Unconfirmed

Similar threads