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,...