mysql - 如何按天数选择访客数

This question already has answers here:

MySQL how to fill missing dates in range?
（4个答案）
三年前关闭。
我有一张桌子，我需要从中选择按日期计数的访客。

CREATE TABLE `visitors` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `ip` VARCHAR(32) NOT NULL,
  `browser` VARCHAR(500) NOT NULL,
  `version` VARCHAR(500) NOT NULL,
  `platform` ENUM('w','l','m') NOT NULL,
  `date` DATE NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `person` (`ip`,`date`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

我试试这个：

SELECT DATE(`date`) AS `date`, COUNT(*) AS `visitor`
FROM `visitors` WHERE (`date` >= (NOW() - INTERVAL 7 DAY))
GROUP BY `date`;

它返回：

date         visitor
2015-12-19    2
2015-12-22    5

但我真正想要的是：

date         visitor
2015-12-17    0        // Make 0 for the day which is not present
2015-12-17    0        // Make 0 for the day which is not present
2015-12-18    0        // Make 0 for the day which is not present
2015-12-19    2
2015-12-20    0        // Make 0 for the day which is not present
2015-12-21    0        // Make 0 for the day which is not present
2015-12-22    5

如果我试着与工会联系，并得到与此相关的日期，那么示例1周就不会有问题。但如果我需要一年，我能做什么？

最佳答案

使用序列引擎。这样就可以生成不带表的日期
https://mariadb.com/kb/en/mariadb/sequence/

MariaDB [mysql]> SELECT date(now() - INTERVAL seq DAY) FROM seq_0_to_6;
+--------------------------------+
| date(now() - INTERVAL seq DAY) |
+--------------------------------+
| 2015-12-22                     |
| 2015-12-21                     |
| 2015-12-20                     |
| 2015-12-19                     |
| 2015-12-18                     |
| 2015-12-17                     |
| 2015-12-16                     |
+--------------------------------+
7 rows in set (0.00 sec)

不用另一台发动机就可以了。您只需要有一个表，其中包含更多记录作为天。倒计时是限制。

SELECT compare_date , count(v.`date`)
FROM (
    SELECT DATE(now() - INTERVAL @nr:=@nr+1 DAY)  AS compare_date
    FROM information_schema.COLUMNS, (SELECT @nr:=-1) AS tmp
    LIMIT 7
    ) AS d
LEFT JOIN  `visitors` v ON v.`date` = compare_date
GROUP BY compare_date;

请告诉我这对你有用。