我的桌子结构就像
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
假设今天是星期一,那么星期范围将更改为最近一周的星期一太阳。
最佳答案
查询将如下所示:
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
关于mysql - 我如何才能找到最近4周的记录数?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/13485860/