求一SQL语句(50分)

C

cooldren

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-05-26

• #1

表A<br>商品编号 &nbsp;数量 &nbsp;总价<br>表B<br>商品编号 商品名称<br><br>汇总表A<br>商品编号 商品名称 &nbsp;总数量 总价格 <br><br>select a.id,数量=sum(a.number),总价=sum(a.amount) from 表A as a group by a.id<br>left join<br>表B as b on a.id=b.id<br><br>在关键字 'left' 附近有语法错误。<br><br>不知道怎么写，盼复，谢谢

 

S

suyike

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-05-26

• #2

select a.id,sum(a.number)数量,sum(a.amount)总价 from 表A as a <br>left join<br>表B as b on a.id=b.id group by a.id<br>--试试？

 

C

Corn3

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-05-26

• #3

select a.id, sum(a.number) as 数量, sum(a.amount) as 总价 from 表A as a <br>left join 表B as b on a.id=b.id<br>group by a.id

 

贝

贝尔

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-05-26

• #4

group by a.id放的位置不对，应该放在最下面。

 

C

cooldren

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-06-02

• #5

是的，group by放到下面可以的<br>但还要显示B表中的商品名称<br><br>select a.id,sum(a.number)数量,sum(a.amount)总价,b.商品名称 from 表A as a <br>left join<br>表B as b on a.id=b.id group by a.id<br><br>这样出现下面的错误<br>&nbsp;'b.商品名称' 在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在 GROUP BY 子句中。

 

W

whrs

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-06-02

• #6

select a.*,b.商品名称 from 表B left join <br>(select id,sum(number),sum(amount) from 表A group by id) 表A1 on b.id=a.id<br>--试试这个

 

李

李翔鹏

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-06-02

• #7

select * from (a.id,数量=sum(a.number),总价=sum(a.amount) from 表A as a group by a.id) a <br>left join<br>表B as b on a.id=b.id

 

C

cooldren

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-06-12

• #8

已经解决，用inner join