php - 我如何在日期和状态之间求和

Question

当前数据库结构payment

ID pending  paid    begin_work  end_work    status
----------------------------------------------------
1  100      20      2011-08-01  2011-08-30  pending 
2  200      1000    2011-08-01  2011-08-15  pending 
3  500      100     2011-08-05  2011-08-25  pending
4  0        200     2011-07-1   2011-08-25  paid  

如何计算当前月份和状态之间的待处理和已支付款项？

$qm = $db->query("
   SELECT
   begin_work, end_work, status,  
   SUM(pending) AS mpending, 
   SUM(paid) AS mpaid
     FROM payment
    ")  

2011 年 8 月 的结果应该是

 pending = 800
 paid    = 1320

Answer 1

你需要一个 group by 子句:

SELECT begin_work, end_work, status, SUM(pending), SUM(paid)
FROM payment
GROUP BY YEAR(begin_work), MONTH(begin_work)