sql - 选择两个日期之间的所有数据

Question

有必要为某些日期选择值，我知道该怎么做，但有一个问题无法解决。

我在 MySQL 中的表:

[User]. [Wins]. [DATE]
#
Ivan ....... 4 ..... 05/06/2010
#
Ivan ....... 3 ..... 06/15/2010
#
Ivan ........ 6 ..... 06/18/2010
#
Ivan ........ 1 ..... 29/06/2010

问题是，如果用户没有访问该站点，则不会在数据库中创建一行，并且错过了获取的日期。

如何获得统计数据(例如 Wins 0)，例如从 06/06/2010 到 06/14/2010 的时间段？帮助进行此查询。

Answer 1

MySQL 没有递归功能，因此您只能使用 NUMBERS 表技巧 -

1. 创建一个只包含递增数字的表 - 使用 auto_increment 很容易做到:

   DROP TABLE IF EXISTS `example`.`numbers`;
   CREATE TABLE  `example`.`numbers` (
     `id` int(10) unsigned NOT NULL auto_increment,
      PRIMARY KEY  (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
   

2. 使用以下方法填充表格:

   INSERT INTO NUMBERS
     (id)
   VALUES
     (NULL)
   

   ...根据您的需要获取尽可能多的值。

3. 使用 DATE_ADD构建日期列表，根据 NUMBERS.id 值增加天数:

   SELECT x.*
     FROM (SELECT DATE_FORMAT(DATE_ADD('2010-06-06', INTERVAL n.id - 1 DAY), '%m/%d/%Y') 
             FROM numbers n
            WHERE DATE_ADD('2010-06-06', INTERVAL n.id -1 DAY) <= '2010-06-14 ) x
   

4. 根据时间部分左连接到您的数据表:

      SELECT x.ts AS timestamp,
             COALESCE(COUNT(y.wins), 0) AS cnt
        FROM (SELECT DATE_FORMAT(DATE_ADD('2010-06-06', INTERVAL n.id - 1 DAY), '%m/%d/%Y') AS ts
                FROM numbers n
               WHERE DATE_ADD('2010-06-06', INTERVAL n.id - 1 DAY) <= '2010-06-14') x
   LEFT JOIN TABLE y ON STR_TO_DATE(y.date, '%m/%d/%Y') = x.ts
    GROUP BY x.ts