sql 语句(20分)

X

xywen

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-04-12

• #1

有一销售 table1 和咨询 TABLE2
TABLE1 TABLE2
TYPE PRICE TYPE ASKNUM
A 12 A 1
B 15 B 2
A 15 C 3
C 14 D 4
D 1
如何查询得到:
TYPE TOTAL-PRICE TOTAL-ASKNUM
A 17 1
B 15 2
C 14 3
D 0 5

TYPE 数量一定.

 

J

Jams

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-04-12

• #2

使用主要-明晰数据库结构即可！？

 

X

xywen

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-04-12

• #3

请具体指教一下!

 

G

g622

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-04-12

• #4

select TABLE1.TYPE,sum(price) TOTAL-PRICE ,sum(ASKNUM) TOTAL-ASKNUM
from TABLE1,TABLE2
where TABLE1.TYPE=TABLE2.TYPE
group by TABLE1.TYPE

 

X

xywen

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-04-12

• #5

我问错了,ASKNUM 总是1,需要用COUNT记数,不过G622的方法不行

 

X

xywen

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-04-12

• #6

是不是得用临时数据库? 没人回答吗?

 

G

g622

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-04-12

• #7

嘿嘿,对不起,多算太多....
简单点,用临时表,如果是access数据库:
select TYPE,sum(price), as TOTAL-PRICE
into tmp1
from TABLE1 group by type
select TYPE,count(price), as TOTAL-asknum
into tmp2
from TABLE2 group by type
select tmp1.type,tmp1.TOTAL-PRICE,tmp2.TOTAL-asknum
from tmp1,tmp2
where tmp1.type=tmp2.type
好像还是有问题,除非你保证两边的type都有,否则会出现空值.

 

X

xywen

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-06-01

• #8

接受答案了.