MS Access查询需求：无缺陷月份将CountOfDEFECT显示为0

问题说明 #

现有数据字段包含AC_TYPE1、CountOfDEFECT、MonthCALOffset、MonthYear。部分机型每月均有缺陷记录，数据显示正常；但部分机型存在部分月份无缺陷的情况，导致这些月份的数据缺失，需要修改查询让这些月份的CountOfDEFECT显示为0。

已知条件：

• MonthCALOffset为数值型，例如2023年6月对应-1，2023年5月对应-2……
• MonthYear为文本型
• 已拥有存储MonthCALOffset与对应日期的日历表（假设表名为Calendar）

当前使用的SQL查询：

SELECT qD1_1.AC_TYPE1, Count(qD1_1.DEFECT) AS CountOfDEFECT, qD1_1.MonthCALOffset, qD1_1.MonthYear
FROM qD1_1
WHERE (qD1_1.MonthCALOffset Between -12 And -1)
GROUP BY qD1_1.AC_TYPE1, qD1_1.MonthCALOffset, qD1_1.MonthYear;

截图显示737-8机型数据正常，而ATR72-600机型因部分月份无缺陷导致数据缺失。另有多个同类查询存在该问题。

修改方案 #

核心思路是先生成所有机型+目标月份的全量组合，再左关联原数据进行统计，从而补全缺失的月份记录，无数据的月份自动填充0。

修改后的SQL示例 #

-- 提取所有需要统计的机型
WITH AllACTypes AS (
    SELECT DISTINCT AC_TYPE1
    FROM qD1_1
),
-- 筛选出需要统计的月份范围（对应MonthCALOffset -12到-1）
TargetMonths AS (
    SELECT MonthCALOffset, MonthYear
    FROM Calendar
    WHERE MonthCALOffset BETWEEN -12 AND -1
)
-- 生成全量机型-月份组合，左关联原数据统计缺陷数
SELECT 
    a.AC_TYPE1,
    COUNT(q.DEFECT) AS CountOfDEFECT,
    t.MonthCALOffset,
    t.MonthYear
FROM AllACTypes a
CROSS JOIN TargetMonths t
LEFT JOIN qD1_1 q 
    ON a.AC_TYPE1 = q.AC_TYPE1 
    AND t.MonthCALOffset = q.MonthCALOffset
GROUP BY a.AC_TYPE1, t.MonthCALOffset, t.MonthYear
ORDER BY a.AC_TYPE1, t.MonthCALOffset;

关键逻辑说明 #

1. 全量组合生成：用CROSS JOIN将所有机型和目标月份配对，确保每个机型的每个统计月份都有基础记录。
2. 左关联补全数据：LEFT JOIN保证即使某机型某月份无缺陷记录，也能保留该组合，不会被过滤掉。
3. 自动填充0：COUNT(q.DEFECT)在无匹配数据时会返回0，因为空值不会被COUNT统计，正好符合需求。

同类查询通用处理方法 #

如果有多个类似查询，只需替换以下部分即可复用逻辑：

• 替换AllACTypes中的机型数据源（如果机型来自其他表）
• 调整TargetMonths中的月份筛选条件
• 替换LEFT JOIN中的原查询表/视图（比如qD1_1换成其他同类视图）
• 同步调整SELECT和GROUP BY中的字段，保持与原查询一致

内容的提问来源于stack exchange，提问作者Selva