如何利用SQL子查询实现“按月统计且包含未发生交易的月份”？-SQL-PHP中文网

直接group by月份会漏掉空月份，因为其仅对实际存在的记录分组，无数据的月份不会生成结果行；必须用递归cte或日期维度表主动构造完整月份序列，再left join补全，并确保起止条件、日期函数及递归深度适配目标数据库。

如何利用sql子查询实现“按月统计且包含未发生交易的月份”？

为什么直接 GROUP BY 月份会漏掉空月份

因为 GROUP BY 只对实际存在的记录分组，没数据的月份根本不会出现在结果里。比如 2024 年 1–12 月，若 2 月和 6 月无交易，那默认查询最多返回 10 行——这不是“按月统计”，是“按有数据的月份统计”。

必须主动构造完整月份序列，再用 LEFT JOIN 补上空值。子查询在这里的核心作用就是生成这个序列。

用递归 CTE 生成连续月份（PostgreSQL / SQL Server / SQLite 3.35+）

这是最可控的方式，不依赖外部表或硬编码。注意：MySQL 8.0+ 也支持，但需确保 max_recursive_depth 足够。

起始月份和结束月份必须明确写成 DATE 或 TEXT 格式（如 '2024-01-01'），不能用 NOW() 直接参与递归，否则可能报错
递归子查询中，UNION ALL 左右两边的列类型、顺序、数量必须严格一致，否则终止递归
别在递归部分加 WHERE 条件（如过滤年份），这会导致跳月或无限循环

WITH RECURSIVE months AS (
  SELECT '2024-01-01'::DATE AS month_start
  UNION ALL
  SELECT month_start + INTERVAL '1 month'
  FROM months
  WHERE month_start <h3>用数字表 + DATE_ADD 模拟月份（MySQL 兼容方案）</h3><p>MySQL 5.7 或未启用 CTE 的环境，常用 <code>information_schema.columns</code> 借行数生成序列。本质是把“生成 12 行”变成“查一个有足够行数的系统表”。</p><div class="aritcle_card flexRow artxards">
											<div class="artcardd flexRow">
												<a class="aritcle_card_img" rel="nofollow" href="/ai/2991" title="墨见"><img
														src="https://img.php.cn/upload/ai_manual/001/246/273/6a3349c71e323532.png" alt="墨见" onerror="this.onerror='';this.src='/static/lhimages/moren/morentu.png'" ></a>
												<div class="aritcle_card_info flexColumn">
													<a rel="nofollow" href="/ai/2991" title="墨见" class="overflowclass">墨见</a>
													<p class="overflowclass">一款AI图像与设计工具，主要用于MockingBot 推出的一人公司 AI 智能体，24 小时陪伴你的全栈虚拟团队，适合需要提升相关任务效率的用户。</p>
												</div>
												<a rel="nofollow" href="/ai/2991" title="墨见" class="aritcle_card_btn flexRow flexcenter"><b></b><span>下载</span>
												</a>
											</div>
										</div>

必须用 LIMIT 控制行数，否则可能拉出几百行
DATE_ADD('2024-01-01', INTERVAL seq.month_num - 1 MONTH) 中的 -1 很关键：否则第 1 行会变成 2024-02-01
JOIN 条件里别用 DATE_FORMAT(t.created_at, '%Y-%m') 做匹配——这会让索引失效；优先用范围比较或 YEAR()/MONTH() 函数（如果字段有索引，函数仍可能使索引不可用，需实测执行计划）

SELECT 
  DATE_ADD('2024-01-01', INTERVAL seq.month_num - 1 MONTH) AS month_start,
  COALESCE(COUNT(t.id), 0) AS transaction_count
FROM (
  SELECT 1 AS month_num UNION SELECT 2 UNION SELECT 3 UNION 
  SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION 
  SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12
) seq
LEFT JOIN transactions t 
  ON YEAR(t.created_at) = YEAR(DATE_ADD('2024-01-01', INTERVAL seq.month_num - 1 MONTH))
 AND MONTH(t.created_at) = MONTH(DATE_ADD('2024-01-01', INTERVAL seq.month_num - 1 MONTH))
GROUP BY seq.month_num
ORDER BY seq.month_num;

WHERE 子句误放导致空月份消失的典型错误

最容易踩的坑：把本该放在 ON 的条件写进主查询的 WHERE。例如想查 2024 年数据，却写成：

... LEFT JOIN transactions t ON ... 
WHERE YEAR(t.created_at) = 2024  -- ❌ 错！这会让 NULL 行被过滤掉

正确做法是把时间条件放进 ON，或在子查询里先过滤 t：

LEFT JOIN (
  SELECT * FROM transactions 
  WHERE created_at >= '2024-01-01' AND created_at <p>否则 <code>t.created_at</code> 为 <code>NULL</code> 时，<code>YEAR(NULL) = 2024</code> 返回 <code>UNKNOWN</code>，整行被踢出结果集——空月份就没了。</p><p>生成月份序列本身不难，难的是让 JOIN 逻辑不破坏 NULL 行；一旦 WHERE 里出现对右表字段的非空判断，就等于主动放弃补全。</p>