mysql - 我如何使用 mysql 获取最近 5 天的总计？

Question

我有这样的表:

id   answers  date    
12   A1       9-nov  
13   A2       10-nov 
14   A3       7-nov  
...                  

现在我想计算每个答案 A1、A2、A3 最近 5 天的总答案。

输出应该是这样的:

answer  count(answer) 
A1      20 
A2      0
A3      34

我如何使用 MySQL 做到这一点？

Answer 1

<罢工>试试这个:

SELECT answers, COUNT(*)
FROM Table1
WHERE DATEDIFF(NOW(), `date`) >= 5
GROUP BY answers;

SQL Fiddle Demo

<罢工>

更新:试试这个:

SELECT t1.answers, IFNULL(COUNT(t2.answers), 0)
FROM table1 t1
LEFT JOIN
(
    SELECT DISTINCT answers FROM table1
) t2 ON  t1.answers = t2.answers AND DATEDIFF(NOW(), t1.`date`) <= 5
GROUP BY t1.answers;

SQL fiddle Demo