如何在 MySQL 中创建秒列表

Question

我有以下查询：

  SELECT timestamp,
         COUNT(*)
    FROM table
GROUP BY timestamp

但有些时间戳没有显示，因为没有数据。这是一个示例

1:00:00 |  3
1:00:02 | 17
1:00:03 |  2

请注意，缺少 1:00:01。有没有办法让 1:00:01 |结果中出现0？

Answer 1

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

1. 创建一个仅保存递增数字的表 - 使用 auto_increment 很容易做到：

   如果存在`example`则删除表。`numbers`;
   创建表“示例”。“数字”(
     `id` int(10) 无符号 NOT NULL 自动增量，
      主键（`id`）
   ) 引擎=InnoDB 默认字符集=latin1;
   

2. 使用以下方式填充表：

   插入数字
     （ID）
   价值观
     （无效的）
   

   ...您需要的任意数量的值。

3. 使用DATE_ADD< /a> 构建时间列表，根据 NUMBERS.id 值增加秒数：

   <前><代码>选择 x.*
   FROM (SELECT DATE_FORMAT(DATE_ADD('2010-01-01', INTERVAL n.id - 1 SECOND), '%T')
   来自数字 n) x
   

4. 根据时间部分 LEFT JOIN 到数据表：

    SELECT x.ts AS 时间戳，
             COALESCE(COUNT(y.timestamp), 0) AS cnt
        FROM (SELECT DATE_FORMAT(DATE_ADD('2010-01-01', INTERVAL n.id - 1 SECOND), '%T') AS ts
                来自数字 n) x
   左连接表 y ON y.timestamp = x.ts
    按 x.ts 分组