这是按小时分组的公式吗？

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 |
+------+------+-----+