按时间统计每个小时的记录数代码,MYSQL代码库,德仔网

代码语言

代码分类

【MYSQL】按时间统计每个小时的记录数

作者: / 发布于2016/12/8/ 744

CREATE TABLE `st` (
  `id` varchar(8) NOT NULL,
  `time` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

insert into st values('F41','2016-08-27 00:25:58');
insert into st values('F42','2016-08-27 01:15:03');
insert into st values('F43','2016-08-27 02:14:30');
insert into st values('F44','2016-08-27 02:24:12');
insert into st values('F45','2016-08-27 03:05:46');
insert into st values('F46','2016-08-27 04:08:07');
insert into st values('F47','2016-08-27 05:10:35');
insert into st values('F48','2016-08-27 07:12:32');
insert into st values('F59','2016-08-27 08:21:19');
insert into st values('F50','2016-08-27 09:19:26');
insert into st values('F51','2016-08-27 11:01:56');
insert into st values('F52','2016-08-27 12:09:38');
insert into st values('F53','2016-08-27 13:42:48');
insert into st values('F54','2016-08-27 14:47:26');
insert into st values('F55','2016-08-27 15:24:12');
insert into st values('F56','2016-08-27 16:22:05');
insert into st values('F57','2016-08-27 18:20:47');
insert into st values('F58','2016-08-27 19:11:09');
insert into st values('F59','2016-08-27 19:41:47');
insert into st values('F60','2016-08-27 19:57:06');
insert into st values('F61','2016-08-27 20:12:45');
insert into st values('F62','2016-08-27 21:55:41');
insert into st values('F63','2016-08-27 22:17:38');
insert into st values('F64','2016-08-27 23:15:17');




需要按小时来统计记录的数量, 结果期望如下:
ID   time   count
------------------------------------------
1  00:00:00  1
2  01:00:00  1
3  02:00:00  1
4  03:00:00  2
5  04:00:00  1
6  05:00:00  1
7  06:00:00  1
8  07:00:00  0
9  08:00:00  1
10 09:00:00  1
11 10:00:00  1
12 11:00:00  0
13 12:00:00  1
14 13:00:00  1
15 14:00:00  1
16 15:00:00  1
17 16:00:00  1
18 17:00:00  1
19 18:00:00  0
20 19:00:00  1
21 20:00:00  3
22 21:00:00  1
23 22:00:00  1
24 23:00:00  1


select  @rownum := @rownum + 1 AS ID,
        concat((case when t.hour = 24 then 0 else t.hour end),':00:00') as time, count(*) as count
from (select id, hour(date_format(time,'%H:%i:%s'))+1 as hour from st) t,
     (SELECT @rownum := 0) r
group by time order by ID

试试其它关键字

同语言下

可能有用的

贡献的其它代码Label