请教一个mysql查询问题，送上100分
表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 9本文来源gaodai#ma#com搞@@代~&码*网/搞gaodaima代码8 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
转载请注明原文链接：请问一个mysql查询有关问题，送上100分
