mysql - 在Mysql中连接2个销售表

Question

我有 2 张表交易 和现金。我将在第一天开具发票，现金可能会分期或多期收到。

我的表格如下所示:

交易表:

date       | customerid | inv_amt 
---------------------------------
2014-12-12 | 1001       | 1000
2014-12-12 | 1002       | 2000

现金表:

date       | customerid | rec_amt 
---------------------------------
2014-12-12 | 1001       | 1000
2014-12-12 | 1002       | 1000
2014-12-13 | 1002       |  500
2014-12-13 | 1003       | 2000

我想加入如下:

date       | customerid  | Inv_amt | cash
-----------------------------------------
2014-12-12 | 1001        | 1000    | 1000
2014-12-12 | 1002        | 2000    | 1000
2014-12-13 | 1002        | Null    |  500
2014-12-13 | 1003        | Null    | 2000

Answer 1

试试这个:

SELECT    a.dates, a.customerid, sum(a.inv_amt), sum(b.rec_amt)
FROM      trans a
LEFT JOIN cash b ON a.dates = b.dates AND a.customerid = b.customerid
GROUP BY  a.dates, a.customerid

UNION

SELECT    b.dates, b.customerid, sum(a.inv_amt), sum(b.rec_amt)
FROM      trans a
RIGHT JOIN cash b ON a.dates = b.dates AND a.customerid = b.customerid
GROUP BY  b.dates, b.customerid