php - 如何按日期对两个mysql表进行分组

Question

如何从两个表中获取按日期显示的报告？

信用表:

------------------------
Date        |   Amount
------------------------
20/10/2014  |    1200
------------------------
10/10/2014  |    3200
------------------------
05/10/2014  |    1300
------------------------

借记表:

------------------------
Date        |   Amount
------------------------
20/10/2014  |    2200
------------------------
15/10/2014  |    5200
------------------------
02/10/2014  |    1800
------------------------

输出应该是:

Date          Cr           Dr
------------------------------
20/10/2014   1200        2200
15/10/2014      0        5200
10/10/2014   3200           0
05/10/2014   1300           0
02/10/2014      0        1800  

我尝试了以下方法:

$date[] = { '20/10/2014','19/10/2014',........Last date };
$i=0;
while ($<sizeof($date)) 
  {
    $credit  = mysql_fetch_assoc(mysql_query("select * from credit where date='".$date[$i]."'"));
    $debit  = mysql_fetch_assoc(mysql_query("select * from debit where date='".$date[$i]."'"));

    echo $credit["amount"]. $debit["amount"];
   }

我在 date 中收集了 from 和 to 日期之间的所有日期。

我知道这是一个糟糕的方法，它会减慢页面的执行速度，那么我怎样才能以更聪明的方式解决这个问题呢？

提前谢谢你。

Answer 1

查询

SELECT * FROM (SELECT a.`Date`,
IFNULL(a.Amount, 0) AS Cr,
IFNULL(b.Amount, 0) AS Dr
FROM Credit a 
LEFT JOIN Debit b
ON a.`Date` = b.`Date`
UNION
SELECT b.`date`, 
IFNULL(a.Amount, 0) AS Cr, 
IFNULL(b.Amount, 0) AS Dr
FROM Credit a
RIGHT JOIN Debit b
ON a.`date` = b.`date`)t
ORDER BY t.`Date` DESC;

Demo