sql中的按小时使用率查询

您可能希望使用创建另一个表(可以是临时的),其中包含表示一天中小时数的数字0-23:

CREATE TABLE hours_of_day (hour int);
INSERT INTO hours_of_day VALUES (1), (2), (3), (4), (5), (6), (7), (8),
                                (9), (10), (11), (12), (13), (14), (15), 
                                (16), (17), (18), (19), (20), (21), (22), (23);

SELECT      hd.hour, COUNT(a.name) `usage`
FROM        hours_of_day hd
LEFT JOIN   activities a ON 
            (hd.hour BETWEEN HOUR(a.start_time) AND HOUR(a.end_time))
GROUP BY    hd.hour;

CREATE TABLE activities (start_time datetime, 
                         end_time   datetime, 
                         name       varchar(10));

INSERT INTO activities VALUES 
     ('2010-01-01 12:10:00', '2010-01-01 13:10:00', 'b'),
     ('2010-01-01 13:20:00', '2010-01-01 13:30:00', 'c'),
     ('2010-01-01 13:50:00', '2010-01-01 14:05:00', 'd'),
     ('2010-01-01 17:20:00', '2010-01-01 20:30:00', 'e');

结果:

+------+-------+
| hour | usage |
+------+-------+
|    1 |     0 |
|    2 |     0 |
|    3 |     0 |
|    4 |     0 |
|    5 |     0 |
|    6 |     0 |
|    7 |     0 |
|    8 |     0 |
|    9 |     0 |
|   10 |     0 |
|   11 |     0 |
|   12 |     1 |
|   13 |     3 |
|   14 |     1 |
|   15 |     0 |
|   16 |     0 |
|   17 |     1 |
|   18 |     1 |
|   19 |     1 |
|   20 |     1 |
|   21 |     0 |
|   22 |     0 |
|   23 |     0 |
+------+-------+
23 rows in set (0.00 sec)

INNER JOIN 而不是 LEFT JOIN . 结果如下:

+------+-------+
| hour | usage |
+------+-------+
|   12 |     1 |
|   13 |     3 |
|   14 |     1 |
|   17 |     1 |
|   18 |     1 |
|   19 |     1 |
|   20 |     1 |
+------+-------+
7 rows in set (0.00 sec)