mysql - 如何计算mysql中组中的记录数

例如，我有一个表1：

+---------------------+
| StartTime           |
+---------------------+
| 2014-04-09 09:47:53 |
| 2014-04-09 09:47:53 |
| 2014-04-09 09:47:55 |
| 2014-04-09 09:47:56 |
| 2014-04-09 09:47:57 |
| 2014-04-09 09:47:59 |
+---------------------+

表没有连续的时间。
例如，它没有
2014-04-09 09 09:47:54，
2014-04-09 09:47:58
等。
然后我使用：

select count(*),StartTime from this_tables group by StartTime;

我还有一张桌子2：

+-------+---------------------+
| count | StartTime           |
+-------+---------------------+
|     2 | 2014-04-09 09:47:53 |
|     1 | 2014-04-09 09:47:55 |
|     1 | 2014-04-09 09:47:56 |
|     1 | 2014-04-09 09:47:57 |
|     1 | 2014-04-09 09:47:59 |
+-------+---------------------+

但是我想要一张桌子3看起来像：

+-------+---------------------+
| count | StartTime           |
+-------+---------------------+
|     2 | 2014-04-09 09:47:53 |
|     0 | 2014-04-09 09:47:54 |
|     1 | 2014-04-09 09:47:55 |
|     1 | 2014-04-09 09:47:56 |
|     1 | 2014-04-09 09:47:57 |
|     0 | 2014-04-09 09:47:58 |
|     1 | 2014-04-09 09:47:59 |
+-------+---------------------+

表3可以包含不存在的START时间的“0”。
我怎么用mysql呢？

最佳答案

你需要一个数字表。您可以按照以下方式创建和填充这样的表（在本例中为100行）

CREATE TABLE tally (n INT NOT NULL AUTO_INCREMENT PRIMARY KEY);

INSERT INTO tally
SELECT a.N + b.N * 10 + 1 n
 FROM
(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
ORDER BY n;

此表中的行数必须与查询的最大可能间隔中的秒数相同。
现在，您要查找的查询（为了这个示例的一天2014-04-09）可能如下所示

SELECT b.starttime, COALESCE(count, 0) count
  FROM
(
  SELECT min_dt + INTERVAL n-1 SECOND starttime
    FROM tally t CROSS JOIN
  (
    SELECT MIN(starttime) min_dt, MAX(starttime) max_dt
      FROM table1
     WHERE starttime >= '2014-04-09'
       AND starttime < '2014-04-09' + INTERVAL 1 DAY
  ) i
   WHERE t.n-1 <= TIMESTAMPDIFF(SECOND, min_dt, max_dt)
) b LEFT JOIN
(
  SELECT starttime, COUNT(*) count
    FROM table1
   WHERE starttime >= '2014-04-09'
     AND starttime < '2014-04-09' + INTERVAL 1 DAY
   GROUP BY starttime
) q
    ON b.starttime = q.starttime

输出：
|开始时间|计数|
|------------------------------|-------|
|2014年4月9日09:47:53+0000 | 2|
|2014年4月9日09:47:54+0000 | 0|
|2014年4月9日09:47:55+0000 | 1|
|2014年4月9日09:47:56+0000 | 1|
|2014年4月9日09:47:57+0000 | 1|
|2014年4月9日09:47:58+0000 | 0|
|2014年4月9日09:47:59+0000 | 1|
这是一个SQLFiddle演示

关于mysql - 如何计算mysql中组中的记录数，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/28621386/