MySQL时间戳字段按年月分组查询异常：仅返回单月数据求解决

原SQL的问题分析 #

1. 仅返回有数据的月份：原WHERE条件仅筛选出2022年存在记录的行，GROUP BY自然只能返回这些有数据的月份，无法显示无用户注册的月份。
2. GROUP BY语法错误：MONTH(FROM_UNIXTIME(registration_date, '%M'))中，%M返回的是月份英文全称（如"January"），MONTH()函数无法正确解析字符串，应直接用MONTH(FROM_UNIXTIME(registration_date))获取数字月份。
3. *SELECT 不符合分组逻辑：GROUP BY后不能直接查询所有字段，需用聚合函数（如COUNT()）统计每组的记录数。

解决方案：生成全月份虚拟表+左连接统计 #

要显示2022年所有12个月（包括无数据的月份），需先生成包含1-12月的虚拟月份表，再与原表的统计结果做左连接。

适用于MySQL 8.0+（支持WITH子句）的SQL

-- 生成2022年1-12月的虚拟月份表
WITH months AS (
    SELECT 1 AS month_num, '2022-01' AS year_month UNION ALL
    SELECT 2, '2022-02' UNION ALL
    SELECT 3, '2022-03' UNION ALL
    SELECT 4, '2022-04' UNION ALL
    SELECT 5, '2022-05' UNION ALL
    SELECT 6, '2022-06' UNION ALL
    SELECT 7, '2022-07' UNION ALL
    SELECT 8, '2022-08' UNION ALL
    SELECT 9, '2022-09' UNION ALL
    SELECT 10, '2022-10' UNION ALL
    SELECT 11, '2022-11' UNION ALL
    SELECT 12, '2022-12'
)
-- 左连接统计每个月的用户数
SELECT
    m.year_month,
    COUNT(t.Id) AS user_count
FROM months m
LEFT JOIN tbl_tax_users t
    ON MONTH(FROM_UNIXTIME(t.registration_date)) = m.month_num
    AND YEAR(FROM_UNIXTIME(t.registration_date)) = 2022
GROUP BY m.year_month, m.month_num
ORDER BY m.month_num;

适用于MySQL 5.x（不支持WITH子句）的SQL

SELECT
    m.year_month,
    COUNT(t.Id) AS user_count
FROM (
    SELECT 1 AS month_num, '2022-01' AS year_month UNION ALL
    SELECT 2, '2022-02' UNION ALL
    SELECT 3, '2022-03' UNION ALL
    SELECT 4, '2022-04' UNION ALL
    SELECT 5, '2022-05' UNION ALL
    SELECT 6, '2022-06' UNION ALL
    SELECT 7, '2022-07' UNION ALL
    SELECT 8, '2022-08' UNION ALL
    SELECT 9, '2022-09' UNION ALL
    SELECT 10, '2022-10' UNION ALL
    SELECT 11, '2022-11' UNION ALL
    SELECT 12, '2022-12'
) m
LEFT JOIN tbl_tax_users t
    ON MONTH(FROM_UNIXTIME(t.registration_date)) = m.month_num
    AND YEAR(FROM_UNIXTIME(t.registration_date)) = 2022
GROUP BY m.year_month, m.month_num
ORDER BY m.month_num;

关键说明 #

• LEFT JOIN：保证所有12个月份都会被返回，无用户注册的月份user_count会显示0。
• 关联条件：同时判断年份和月份，避免其他年份的同月份数据被错误统计。
• COUNT(t.Id)：用具体字段计数，忽略LEFT JOIN后无数据行的NULL值，得到准确的0值。

内容的提问来源于stack exchange，提问作者Muhammad Shafique Attari