连接2列以创建账户流查询

Question

我有两个表，一个用于客户的信用(付款)，它看起来像这样：

id     client     date              amount
1       ana       2012-01-01        5000  
2       ana       2012 02-01        10000
3       ana       2012-03-01        5000

等等。

另一个用于借记(提款)的表如下所示：

id    client      date             amount 
1       ana       2012-01-15        5000
2       ana       2012 02-15        8000
3       ana       2012-03-15        9000

等等。

我想生成一个按日期对每个客户端的所有事务进行排序的查询，将两个日期列合并为一个，然后按日期排序，它应该如下所示：

date           payment     withdrawal 
2012-01-01     5000           ----
2012-01-15     -----          5000
2012-02-01     10000          ----  
2012-02-15     -----          8000
2012-03-01      5000          ----
2012-03-15     -----          9000

这就是我尝试的，但我知道它是错误的，它给出了重复的记录：

SELECT payments.amount, payments.date withdrawals.amount, withdrawals.date
FROM payments,withdrawals 
WHERE payments.client = withdrawals.client
ORDER BY date

我尝试了左连接，但它给出了相同的结果：

 SELECT payments.amount, payments.date, withdrawals.amount, withdrawals.date
 FROM payments 
 LEFT JOIN withdrawals 
 ON payments.client= withdrawals.client
 ORDER BY date

请帮忙，谢谢！

Answer 1

您需要执行两个简单的SELECT查询，分别针对每个表执行一个查询，然后使用UNION关键字组合结果，如下所示：

SELECT date, amount AS payment, NULL AS withdrawal FROM payments WHERE client = 'ana'
UNION ALL
SELECT date, NULL AS payment, amount AS withdrawal FROM withdrawals WHERE client = 'ana'
ORDER BY date

您不希望执行任何类型的连接，因为每行的数据只从一个表中拉出，而不是从另一个表中拉出。

Answer 2

您正在寻找的是一个完整的外部联接：

SELECT payments.amount, payments.date, withdrawals.amount, withdrawals.date
FROM payments FULL OUTER JOIN withdrawals
  ON payments.client= withdrawals.client
Order by date

或者使用UNION (根据MySQL的需要)：

SELECT payments.amount, payments.date, withdrawals.amount, withdrawals.date
FROM payments LEFT OUTER JOIN withdrawals
  ON payments.client= withdrawals.client
Order by date
UNION
SELECT payments.amount, payments.date, withdrawals.amount, withdrawals.date
FROM payments RIGHT OUTER JOIN withdrawals
  ON payments.client= withdrawals.client
Order by date