sql查询数据，如何查询出一天中每小时的数据cout？

Question

各位，我数据库里created_time存的是字符串类型，如何查询一天当中每小时的数据量，这sql怎么写？

Answer 1

mysql> desc test2;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | YES  |     | NULL    |       |
| time_rec | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

mysql> select * from test2;
+------+---------------------+
| id   | time_rec            |
+------+---------------------+
|    1 | 2018-12-12 12:12:12 |
|    2 | 2018-12-12 12:12:14 |
|    3 | 2018-12-12 13:12:12 |
|    4 | 2018-12-13 12:12:12 |
+------+---------------------+
4 rows in set (0.00 sec)

mysql> select count(*),left(time_rec,13) as hour_time from test2 where left(time_rec,10)='2018-12-12' group by hour_time; 
+----------+---------------+
| count(*) | hour_time     |
+----------+---------------+
|        2 | 2018-12-12 12 |
|        1 | 2018-12-12 13 |
+----------+---------------+
2 rows in set (0.00 sec)

Answer 2

可以使用分段查询：

select * from 
   (select count(*) from you_table where 第一个小时的区间 ) as one,
   (select count(*) from you_table where 第二个小时的区间 ) as two,
   ...