mysql - 按范围对 24 小时时间进行分组

Question

我有很多像这样 24 小时格式的行

我想要的是这样的格式

+------+------+----------+
| from | to   | count(*) |
+------+------+----------+
| 0000 |  0100|        3 |
| 0100 | 0200 |        4 |
|  0200| 0300 |        2 |
| 0300 | 0400 |        1 |
+------+------+----------+

我已经尝试过了

SELECT COUNT(*) `count`,
       100*FLOOR(the_amount/100) `from`,
       100*FLOOR(the_amount/100)+99 `to`
  FROM r_data
 where transaction_type = 'send'
 GROUP BY FLOOR(the_amount/100) ORDER BY COUNT(*) DESC

但这只给出一行。我应该如何编写一个查询来对 24 小时格式的时间进行分组？

Answer 1

试试这个:

SELECT
  LPAD(100*FLOOR(24_hour_time/100),4,0) AS `from`,
  LPAD(100*FLOOR(24_hour_time/100) + 100,4,0) AS `to`,
  COUNT(*) AS `count`
FROM
  r_data
WHERE
  transaction_typ = 'send'
GROUP BY 1
ORDER BY 24_hour_time;

但它只会显示“填充时间”。