sql多表查询难题，高手来解决，300分。(300)

W

wangfeisfw

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-11-30

• #1

表A有字段id和name数据001 name1002 name2003 name3表B有字段ID和RecQuantity数据 001 200 001 100 002 300 003 500表B有字段ID和SaleQuantity数据 001 50 001 50 001 100 002 100怎样查询得到 name totalRec totalsale name1 300 200 name2 300 100 name3 500 上面类似的表结构的查询语句该怎么写，还有关联的其他表，谁先把这个解决。。。。

 

阿

阿九

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-11-30

• #2

说实在的，这个真的不难....这是最基础的SQL查询select a.id, sum(b.RecQuantity) totalRec , sum(c.SaleQuantity) totalsale from aleft outer join b on b.id=a.idleft outer join c on c.id=a.id

 

L

lht123

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-11-30

• #3

对，阿九的答案对，就是分组聚合在连接 就可以了，很简单的，你试试

 

L

lzh1983

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-11-30

• #4

随便写了一个，请大家指教，谢谢！select a.id, temp1.totalRec, temp2.totalSale from aleft join (select * from (select id, sum(b.RecQuantity) totalRecfrom bgroup by id) temp) temp1 on temp1.id = a.idleft join(select * from (select id, sum(c.SaleQuantity) totalsalefrom cgroup by id) temp) temp2 on temp2.id = a.id

 

A

aoelwg

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-11-30

• #5

好久没写过这个了，不知道这样写，楼主是否可以看得更明白些select a.name1,TmpB.totalRec,TmpC.totalSalefrom a left join (select id,sum(RecQuantity) as totalRec from b group by id) TmpB on a.id = TmpB.id left join (select id,sum(SaleQuantity) as totalsale from c group by id) TmpC on a.id = TmpC.id

 

L

lizhenhe

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-12-01

• #6

select a.id, sum(b.RecQuantity) totalRec , sum(c.SaleQuantity) totalsale from aleft outer join b on b.id=a.idleft outer join c on c.id=a.idgroup by a.idorder by a.id

 

W

weibinggui

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-12-01

• #7

呵呵！阿九是对的！

 

E

eloveme

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-12-01

• #8

select 表a.name, temp1.totalRec, temp2.totalSale from 表aleft join (select 表b.id, sum(表b.RecQuantity) totalRec from 表b group by 表b.id) temp1 on temp1.id = a.idleft join(select 表c.id, sum(表c.SaleQuantity) totalsalefrom 表c group by 表c.id) temp2 on temp2.id = 表a.id

 

G

GodIsWrong

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-12-01

• #9

select a.name1,TmpB.totalRec,TmpC.totalSalefrom a left join (select id,sum(RecQuantity) as totalRec from b group by id) TmpB on a.id = TmpB.id left join (select id,sum(SaleQuantity) as totalsale from c group by id) TmpC on a.id = TmpC.id

 

P

power255

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-12-01

• #10

select A.name, sum(tmp.RecQuantity) totalRec, sum(tmp.SaleQuantity) totalsale from 表A A, (select ID, RecQuantity, 0 SaleQuantity from 表B union all select ID, 0 RecQuantity, SaleQuantity from 表C) TMP where A.ID = TMP.ID group by A.name

 

M

michael.ho

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-12-01

• #11

ddd

 

N

npcd

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-12-01

• #12

--Oracle数据库select a.name,tmp1.SumRecQuantity totalRec,tmp2.SumSaleQuantity totalsale from a, (select id,sum(RecQuantity) SumRecQuantity from b group by id) tmp1, (select id,sum(SaleQuantity) SumSaleQuantity from c group by id) tmp2 where tmp1.id(+)=a.id and tmp2.id(+)=a.id

 

Z

zbdzjx

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-12-02

• #13

其它几个人的还差不多，阿九的肯定不对，会多倍。

 

E

eloveme

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-12-03

• #14

无语，这种帖子还能不结！！