mysql - 仅一年数据

Question

这是我的 table 捐赠。

SELECT nid, SUM(amount) as sum, month 
FROM `donate` 
where nid = 1221 
  and payment_status = 'completed'
  and date >= 1388534400
group by month

+----------------------------------------------------+
| nid  | amount| date       | month | payment_status |
| 1221 |  80   | 1388534400 | jan-14 | completed     |
| 1221 |  60   | 1388534400 | jan-14 | completed     |
| 1221 |  90   | 1388534400 | apr-14 | completed     |
| 1221 |  80   | 1388534400 | aug-14 | completed     |
| 1221 |  90   | 1388534400 | dec-14 | completed     |
| 1221 |  100  | 1388534400 | dec-13 | completed     |
+----------------------------------------------------+

1388534400 是 2014 年 1 月 1 日星期三 00:00:00 GMT 的日期戳

我只想获取一年的数据，现在如果我运行上述查询，那么我将获得我不想要的 12 月 13 日的数据。

+--------+-------+------+---------+----+---------+
| 1221|  100  |dec-13 |completed      |
+--------+-------+------+---------+----+---------+

我希望数据只能提供一年。仅此而已。

Answer 1

尝试使用DATE_SUB :

SELECT nid, SUM(amount) as sum, month  FROM `votesane_donate` 
WHERE nid = 1221 
AND payment_status = 'completed'
AND date >= DATE_SUB(CURRENT_DATE, INTERVAL 1 YEAR)
GROUP BY month

假设date字段是TIMESTAMP类型，而不是INTEGER。