如何计算mysql中一个组中的记录数 [英] how to count the num of records in a group in mysql

问题描述

例如，我有一个table1：

  + -------------- ------- + 
 | 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:47:54，

2014-04-09 09:47:58

等。
然后我使用：

  select count（*），StartTime from this_tables group by StartTime; 
  

我得到另一个table2：

  + ------- + --------------------- + 
 |计数| 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 | 
 + ------- + --------------------- + 
  

但我想要一个table3看起来像：

  ----- + --------------------- + 
 |计数| 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对于不存在的StartTime可以包含0。
我如何使用mysql？

解决方案

你需要一个数字您可以使用以下方式创建和填充此表（在本例中为100行）

  CREATE TABLE tally（n INT NOT NULL AUTO_INCREMENT PRIMARY KEY）; 
 
 INSERT INTO tally 
 SELECT aN + bN * 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 UNIVERS ALL SELECT ALL UNION ALL SELECT UNION ALL SELECT 3 UNION ALL SELECT 3 UNION ALL SELECT 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开始时间<'2014-04-09'+ INTERVAL 1天$ ​​b $ b）i 
 WHERE tn-1< = TIMESTAMPDIFF（SECOND，min_dt，max_dt）
）b LEFT JOIN 
（
 SELECT starttime，COUNT（*）count 
 FROM table1 
 WHERE starttime> ='2014-04-09'
 AND开始时间<'2014-04-09'+ INTERVAL 1 DAY 
 GROUP BY开始时间
）q 
 ON b.starttime = q.starttime 
  

输出：

 
 | STARTTIME | COUNT | 
 | ------------------------------ | ------- | 
 | April，09 2014 09：47：53 + 0000 | 2 | 
 | April，09 2014 09：47：54 + 0000 | 0 | 
 | April，09 2014 09：47：55 + 0000 | 1 | 
 | April，09 2014 09：47：56 + 0000 | 1 | 
 | April，09 2014 09：47：57 + 0000 | 1 | 
 | April，09 2014 09：47：58 + 0000 | 0 | 
 | April，09 2014 09：47：59 + 0000 | 1 | 
 

这是 SQLFiddle 演示

For example, I have a table1:

+---------------------+
| 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 |
+---------------------+

tables doesn't have a consecutive time.
For example,it doesn't have

2014-04-09 09:47:54,
2014-04-09 09:47:58

etc. Then I use:

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

I get another table2:

+-------+---------------------+
| 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 |
+-------+---------------------+

But I want get a table3 looks like:

+-------+---------------------+
| 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 |
+-------+---------------------+

Table 3 can contain '0' for that StartTime which doesn't exist. How could I do with mysql?

解决方案

You'll need a numbers (tally) table for that. You can create and populate such table (in this case with 100 rows) the following way

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;

You'll need to have as many rows in this table as the number of seconds in the largest possible interval that you query for.

Now the query (for the sake of this example for one day 2014-04-09) that you're looking for may look like this

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

Output:

|                    STARTTIME | COUNT |
|------------------------------|-------|
| April, 09 2014 09:47:53+0000 |     2 |
| April, 09 2014 09:47:54+0000 |     0 |
| April, 09 2014 09:47:55+0000 |     1 |
| April, 09 2014 09:47:56+0000 |     1 |
| April, 09 2014 09:47:57+0000 |     1 |
| April, 09 2014 09:47:58+0000 |     0 |
| April, 09 2014 09:47:59+0000 |     1 |

Here is a SQLFiddle demo

这篇关于如何计算mysql中一个组中的记录数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！