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

Question

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

Answer 1

mysql>desctest2;
+----------+-------------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+----------+-------------+------+-----+---------+-------+
|id|int(11)|YES||NULL||
|time_rec|varchar(20)|YES||NULL||
+----------+-------------+------+-----+---------+-------+
mysql>select*fromtest2;
+------+---------------------+
|id|time_rec|
+------+---------------------+
|1|2018-12-1212:12:12|
|2|2018-12-1212:12:14|
|3|2018-12-1213:12:12|
|4|2018-12-1312:12:12|
+------+---------------------+
4rowsinset(0.00sec)
mysql>selectcount(*),left(time_rec,13)ashour_timefromtest2whereleft(time_rec,10)='2018-12-12'groupbyhour_time;
+----------+---------------+
|count(*)|hour_time|
+----------+---------------+
|2|2018-12-1212|
|1|2018-12-1213|
+----------+---------------+
2rowsinset(0.00sec)
                            

Answer 2

可以使用分段查询：
select*from
(selectcount(*)fromyou_tablewhere第一个小时的区间)asone,
(selectcount(*)fromyou_tablewhere第二个小时的区间)astwo,
...