本文详解如何通过 cross join 构建完整时间维度 + left join 关联业务表，配合 coalesce 实现“无匹配行时聚合结果为 0”的需求，彻底解决 inner join 导致的月份/分类缺失问题。
本文详解如何通过 cross join 构建完整时间维度 + left join 关联业务表，配合 coalesce 实现“无匹配行时聚合结果为 0”的需求，彻底解决 inner join 导致的月份/分类缺失问题。
在 SQL 聚合分析中，一个常见但易被忽视的问题是：当使用 INNER JOIN 关联主表（如分类维度表）与事实表（如损耗记录表）时，若某分类在指定时间段内完全没有对应记录，该分类+月份组合将直接从结果集中消失——此时 ISNULL() 或 COALESCE() 对 SUM() 的包裹完全无效，因为根本不存在该分组的行可供计算。
您原始查询中使用 INNER JOIN，导致 Benang Keluar 缺失 2022-04、Belang 缺失 2022-06 等情况，正是此问题的典型表现。关键不在于 ISNULL 还是 COALESCE（二者在此场景下效果一致），而在于必须先保证所有期望的维度组合（部门+分类+月份）在结果集中存在。
✅ 正确解法：显式构造维度 + LEFT JOIN
核心思路是：
- 主动构建完整的月份维度（如 '2022-04', '2022-05', '2022-06'）；
- 将该维度与分类主表 TBL_CATEGORY 进行 CROSS JOIN，生成所有可能的 (category, month) 组合；
- 再以该组合为基准，LEFT JOIN 事实表 B_ENTRYLOSS，确保即使无匹配数据，行依然保留；
- 最后对 SUM() 使用 COALESCE(..., 0) 安全兜底。
以下是优化后的标准写法：
SELECT
TCS.departement,
TCS.category,
m.mnth AS CURR_MONTH,
COALESCE(SUM(CASE
WHEN BE.DFROM IN ('SEWING','WEAVING','FORMING','DYE','HSM','INSPECTION','WOVEN')
AND BE.DFROM = BE.DTO
THEN BE.QTY
END), 0) AS TOTAL_LOSS_PROSES,
COALESCE(SUM(CASE
WHEN BE.DFROM IN ('SEWING','WEAVING','FORMING','DYE','HSM','INSPECTION','WOVEN')
THEN 0
ELSE BE.QTY
END), 0) AS TOTAL_LOSS_SEMUA
FROM TBL_CATEGORY TCS
CROSS JOIN (
SELECT '2022-04' AS mnth
UNION ALL
SELECT '2022-05'
UNION ALL
SELECT '2022-06'
) AS m
LEFT JOIN B_ENTRYLOSS BE
ON TCS.category = BE.LOSSCAT
AND FORMAT(BE.DT, 'yyyy-MM') = m.mnth
GROUP BY TCS.departement, TCS.category, m.mnth
ORDER BY TCS.category ASC, CURR_MONTH DESC;? 关于 CASE 逻辑的澄清
您观察到两种写法结果不同，根源在于逻辑错误：
-
❌ 错误写法（多个 WHEN DFROM != 'X'）：
WHEN DFROM != 'SEWING' THEN QTY WHEN DFROM != 'WEAVING' THEN QTY
这是永远成立的冗余条件——只要 DFROM 不等于 'SEWING'，第一行就返回 QTY，后续条件永不执行。等价于 ELSE QTY，造成统计口径混乱。
-
✅ 正确写法（NOT IN 或 IN 取反）：
WHEN BE.DFROM NOT IN ('SEWING','WEAVING',...) THEN BE.QTY -- 或更清晰的等价写法（推荐）： WHEN BE.DFROM IN ('SEWING','WEAVING',...) THEN 0 ELSE BE.QTY语义明确：仅当 DFROM 不属于指定工序列表时才计入 TOTAL_LOSS_SEMUA。
⚠️ 注意事项
- COALESCE 优于 ISNULL：COALESCE 是 SQL 标准函数，支持多参数且类型推导更严谨；ISNULL 是 SQL Server 特有，仅支持两参数且返回第一个参数类型。
- 避免 FORMAT() 在 JOIN 条件中：FORMAT(BE.DT, 'yyyy-MM') 无法利用 DT 字段索引，建议在 B_ENTRYLOSS 表中预先添加计算列或使用 YEAR(DT)*100 + MONTH(DT) 等可索引方式。
- 动态月份生成：若需支持任意日期范围，可用递归 CTE 或日历表替代硬编码 UNION ALL。
通过以上结构化设计，您将获得真正“全维度覆盖”的聚合结果——每个部门、每个分类、每个月份均稳定输出，缺失值自动补零，彻底告别因 INNER JOIN 导致的数据遗漏陷阱。
