php - 每周检索数据

Question

我只是想问是否有一个查询可以从下表中检索数据，如下所示:-

每周我都想显示客户支付的总金额。所以我想每周检查每个客户的资金情况。

Customer  |   Date      |Money|
----------------------------------------------------------------------
Jhon        2014-07-01    22
Ammy        2014-07-02    25
Jhon        2014-07-03    21
Josh        2014-07-04    28
Josh        2014-07-05    30
Ammy        2014-07-06    211
Jhon        2014-07-07    217

谢谢...

Answer 1

您可以使用GROUP BY和WEEK函数来做到这一点:

SELECT customer, WEEK(date) AS week, SUM(money) AS total
FROM table
GROUP BY customer, WEEK(date);

这是sqlfiddle:http://sqlfiddle.com/#!2/f0754f/1

<小时/>

如果您只想本周，您可以添加 WHERE 子句:

SELECT customer, WEEK(date) AS week, SUM(money) AS total
FROM table
WHERE WEEK(date) = WEEK(NOW())
GROUP BY customer, WEEK(date);