mysql - 我如何找出最近 4 周的记录数？

Question

我的表结构是这样的

CREATE TABLE `survey` (
  `id` int(11) NOT NULL auto_increment,
  `submitdate` datetime default NULL,
 `answer` varchar(5) collate utf8_unicode_ci default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=499 ;

现在我想统计过去 4 周的记录数。

一件事是如果假设在特定的一周没有记录它应该列为 0

这里的例子输出是这样的

count(*) week 
  0      mon-sun  //29 oct -  4 nov every week starts from monday and ends with sunday 
  4      mon-sun  //5 nov -  11 nov
  45     mon-sun  //12 nov -  18 nov  last week 
  0      mon-sun  //18 nov - 25 nov  latest week 

假设今天是星期一，然后周范围更改为最近一周的周一至周日。

Answer 1

查询看起来像:

SELECT
    s.id,
    DATE(DATE_SUB(s.submitdate,INTERVAL(DAYOFWEEK(s.submitdate) - 2) DAY)) AS weekStart,
    ADDTIME(SUBTIME(SUBDATE(s.submitdate,INTERVAL(DAYOFWEEK(s.submitdate) - 8)DAY),TIME(s.submitdate)),MAKETIME(23,59,59)) AS weekEnd,
    COUNT(*) AS total
FROM survey s
GROUP BY weekStart
LIMIT 0,3