請問一個mysql查詢有關問題，送上100分

請教一個mysql查詢問題，送上100分
本帖最後由 SHANDIANDIAN 於 2013-12-12 12:15:27 編輯

表A 表B
uid mid cash uid mid cash number
1 1 100 1 1 101 100
2 2 105 2 1 100 100
3 3 98 3 2 105 200
4 4 55 4 2 105 300
5 5 60 5 3 60 300
6 6 70 6 3 98 300

uid 是自動成長欄位 mid和cash 是關聯欄位

我想統計出 表B的求和數量就和下面這樣，沒匹配到表A就顯示0，匹配通過mid和cash 兩欄位。請問SQL該怎麼寫？？ 以表A為基礎資料表
結果表
uid mid cash number
1 1 100 100
2 2 105 500
3 3 98 300
4 4 55 0
5 5 60 0
6 6 70 0

分享到： 更多

------解決方案--------------------
你太調皮，後來編輯過文章了
看這個
http://sqlfiddle.com/#!2/9847b/12
------解決方案--------------------

create temporary table A
select 1 as uid, 1 as mid, 100 as cash
union select 2, 2, 105
union select 3, 3,  98
union select 4, 4,  55
union select 5, 5,  60
union select 6, 6,  70;

create temporary table B
select 1 as uid, 1 as mid, 101 as cash, 100 as  number
union select 2,     1,   100,    100
union select 3,     2,   105,    200
union select 4,     2,   105,    300
union select 5,     3,   60,     300
union select 6,     3,   98,     300;

select uid,mid, cash, (select sum(number) from B where mid=A.mid and cash=A.cash) from A

1 1 100 100
2 2 105 500
3 3 98 300
4 4 55
5 5 60
6 6 70

寫測試資料花的時間比寫查詢指令的時間多十倍都不止

------解決方案--------------------

select a.uid, a.mid, a.cash, sum(b.number) from testa as a left join testb as b on a.mid=b.mid and a.cash = b.cash group by a.mid, a.cash

這個返回結果是
uid mid cash sum(b.number)
11100100
22105500
3398300
4455
5560
6670

