MYSQL按照小时；按天；按月分组，无数据补0

可以使用 MySQL 的日期函数和 GROUP BY 子句来按照小时、天和月分组。

按小时分组：

SELECT DATE_FORMAT(date_column, '%Y-%m-%d %H:00:00') as hour, COUNT(*) as count
FROM table_name
GROUP BY hour;

按天分组：

SELECT DATE(date_column) as day, COUNT(*) as count
FROM table_name
GROUP BY day;

按月分组：

SELECT DATE_FORMAT(date_column, '%Y-%m') as month, COUNT(*) as count
FROM table_name
GROUP BY month;

如果需要在无数据的情况下补0，可以使用 MySQL 的 IFNULL 函数和子查询来实现：

按小时分组：

SELECT hours.hour, IFNULL(data.count, 0) as count
FROM (
    SELECT DATE_FORMAT(date_column, '%Y-%m-%d %H:00:00') as hour
    FROM table_name
    GROUP BY hour
) as hours
LEFT JOIN (
    SELECT DATE_FORMAT(date_column, '%Y-%m-%d %H:00:00') as hour, COUNT(*) as count
    FROM table_name
    GROUP BY hour
) as data ON hours.hour = data.hour;

按天分组：

SELECT days.day, IFNULL(data.count, 0) as count
FROM (
    SELECT DATE(date_column) as day
    FROM table_name
    GROUP BY day
) as days
LEFT JOIN (
    SELECT DATE(date_column) as day, COUNT(*) as count
    FROM table_name
    GROUP BY day
) as data ON days.day = data.day;

按月分组：

SELECT months.month, IFNULL(data.count, 0) as count
FROM (
    SELECT DATE_FORMAT(date_column, '%Y-%m') as month
    FROM table_name
    GROUP BY month
) as months
LEFT JOIN (
    SELECT DATE_FORMAT(date_column, '%Y-%m') as month, COUNT(*) as count
    FROM table_name
    GROUP BY month
) as data ON months.month = data.month;