Ask a MySQL query about the problem, send 100 points

Ask a MySQL query question, send 100 points
This post was last edited by Shandiandian on 2013-12-12 12:15:27

Table A table 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 is auto-grow field mid and cash are associated fields

I want to count the sum of table B as follows, no match to table A shows 0, matches the mid and cash two fields. How can I write sql?? Table A as the base table
Results table
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

Share to: more

------Solution--------------------
You are too naughty, and then you edited the post.
Look at this
Http://sqlfiddle.com/#!2/9847b/12
------Solution--------------------

Create temporary table A
Select 1 as UID, 1 as Mid, 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, as number  
Union select 2,     1,    100
Union Select 3,     2,    200
Union Select 4,     2,    300
Union Select 5,     3,     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

Writing test data takes more than 10 times times more time than writing query instructions.

------Solution--------------------

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 B Y A.mid, A.cash

The return result is
UID Mid cash sum (b.number)
11100100
22105500
3398300
4455
5560
6670

