这个小组是按小时计算的吗？

Question

N -要分组的小时数

DATETIME列值(如，2011-10-08 21:23:43)

注:用于Mysql DB

GROUP BY date( `DATETIME` ) , N* floor( date_format( `DATETIME` , '%H' ) /N ))

1. 是按小时分组吗？
2. 按日/月计算组的公式吗？

Answer 1

SELECT MONTH(dt) as m, DAY(dt) as d, COUNT(*) as cnt
FROM (
SELECT '2011-10-11 10:00:00' as dt
UNION ALL
SELECT '2011-10-11 20:00:00' as dt
UNION ALL
SELECT '2011-10-12 10:00:00' as dt
UNION ALL
SELECT '2011-10-12 20:00:00' as dt
UNION ALL
SELECT '2011-10-13 10:00:00' as dt
UNION ALL
SELECT '2011-10-13 20:00:00' as dt
UNION ALL
SELECT '2011-11-13 10:00:00' as dt
UNION ALL
SELECT '2011-11-13 20:00:00' as dt
) as dates
GROUP BY m, d;

在以下方面的成果：

+------+------+-----+
| m    | d    | cnt |
+------+------+-----+
|   10 |   11 |   2 |
|   10 |   12 |   2 |
|   10 |   13 |   2 |
|   11 |   13 |   2 |
+------+------+-----+