请问一个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<br />
select 1 as uid, 1 as mid, 100 as cash<br />
union select 2, 2, 105<br />
union select 3, 3,  98<br />
union select 4, 4,  55<br />
union select 5, 5,  60<br />
union select 6, 6,  70;<br />
<br />
create temporary table B<br />
select 1 as uid, 1 as mid, 101 as cash, 100 as  number<br />
union select 2,     1,   100,    100<br />
union select 3,     2,   105,    200<br />
union select 4,     2,   105,    300<br />
union select 5,     3,   60,     300<br />
union select 6,     3,   98,     300;<br />
<br />
select uid,mid, cash, (select sum(number) from B where mid=A.mid and cash=A.cash) from A<br />

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)
1 1 100 100
2 2 105 500
3 3 98 300
4 4 55
5 5 60
6 6 70