mysql - Mysql连接查询？

ToBeCollected表

Id     Name          Amount
1      MR XXX         10000
2      Mr YYY         15000
3      Mr ZZZ         12000

收集的表

ID    Name        Amount
1     Mr XXX       5000
2     Mr XXX       2000
3     Mr YYY      12000
4     Mr YYY       1000

我要找的是这个。

ID    Name   ToBeCollected    Collected     Balance
1    Mr XXX     10,000           7,000       3,000
2    Mr YYY     15,000          13,000       2,000
3    Mr ZZZ     12,000               0      12,000

最佳答案

尝试此查询…

SELECT t.id,t.name,max(t.amt) as tobeCol,
       coalesce(s.amt,0) as Col,
       max(t.amt) - coalesce(s.amt,0) as Balance
FROM ToBeCollected t
LEFT JOIN (select sum(amt) as amt,name from Collected group by name) s on s.name=t.name
group by t.name,t.id;

关于mysql - Mysql连接查询？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/37001303/