我如何用mysql获得最近5天的总数？

I have table like:

我有这样的表:

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

Now I want to calculate the total answer of last 5 days for each answers A1, A2, A3.

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

The output should be like:

输出应该像:

answer  count(answer) 
A1      20 
A2      0
A3      34

How can I do that with MySQL?

我怎么能用MySQL做到这一点?

2 个解决方案

#1

Try this:

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

SQL Fiddle Demo

Update: Try this instead:

更新:尝试这样做:

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

#2

What do you mean with the last 5 days? do you mean the last 5 days since now, or the last 5 days present in your table? If you mean the last 5 days present in your table, which can be more than 5 days ago if you miss some days in your table, then you could use something like this:

你最近5天是什么意思?你的意思是从现在开始的最近5天,或你桌上的最后5天?如果你的意思是表格中存在的最后5天,如果你错过了表中的某些日子,这可能超过5天前,那么你可以使用这样的东西:

SELECT
  ans.answers, count(last_tab.id)
FROM
  (select distinct answers from tab) ans
  left join
  (select id, answers
   from tabs
        inner join
        (select distinct date from tab order by date desc limit 5) dt
  on tab.date = dt.date) last_tab
  on ans.answers = last_tab.answers
group by ans.answers;