如何修改Left Outer Join查询避免设备月度成本数据遗漏？

问题核心是主设备表缺少年月维度，导致无法覆盖所有设备-年月的组合，原查询仅通过关联ONMARC结果获取年月，会丢失那些只有OFFMARC记录、或该年月无任何成本的设备-年月条目。

修改思路：先构造目标年月的完整集合，再与设备表做交叉连接生成所有设备-年月的基础条目，最后分别关联两类成本的汇总数据。

修改后的查询语句 #

-- 1. 构造目标年月的维度集合
WITH TargetMonths AS (
    SELECT 1 AS Month, 2024 AS Year UNION ALL
    SELECT 2 AS Month, 2024 AS Year
),
-- 2. 预计算ONMARC的成本汇总
ONMARCSummary AS (
    SELECT 
        SUM(Cost) AS TCost, 
        Month, 
        Year, 
        RIGHT(EquipmentID,9) AS Equipment_Numnber -- 提前转换为和设备表匹配的格式
    FROM Marc_Cost 
    WHERE Year = 2024 AND Month <= 2 AND Cost_Element = '0000555090' 
    GROUP BY Month, Year, RIGHT(EquipmentID,9) -- 无需按Cost_Element分组，过滤条件已限定唯一值
),
-- 3. 预计算OFFMARC的成本汇总
OFFMARCSummary AS (
    SELECT 
        SUM(Cost) AS TCost, 
        Month, 
        Year, 
        RIGHT(EquipmentID,9) AS Equipment_Numnber
    FROM Marc_Cost
    WHERE Year = 2024 AND Month <= 2 AND Cost_Element <> '0000555090' 
    GROUP BY Month, Year, RIGHT(EquipmentID,9)
)
-- 4. 生成所有设备-年月组合，再关联两类成本
SELECT 
    eq.Equipment_Numnber,
    eq.EquipmentDesc,
    IFNULL(om.TCost, 0) AS ONMARC,
    IFNULL(ofm.TCost, 0) AS OFFMARC,
    tm.Month,
    tm.Year
FROM v_marc_equipment eq
CROSS JOIN TargetMonths tm -- 交叉连接得到每个设备对应每个目标年月的记录
LEFT JOIN ONMARCSummary om 
    ON eq.Equipment_Numnber = om.Equipment_Numnber 
    AND tm.Month = om.Month 
    AND tm.Year = om.Year
LEFT JOIN OFFMARCSummary ofm 
    ON eq.Equipment_Numnber = ofm.Equipment_Numnber 
    AND tm.Month = ofm.Month 
    AND tm.Year = ofm.Year
ORDER BY tm.Year, tm.Month, eq.Equipment_Numnber;

关键优化点 #

• TargetMonths CTE：明确列出需要统计的年月，不依赖成本表中的现有数据，确保覆盖所有目标时间范围。
• CROSS JOIN：生成设备表与年月维度的全量组合，保证每个设备在每个目标年月都有一条基础记录，彻底解决记录遗漏问题。
• 提前转换EquipmentID：在汇总阶段就把EquipmentID转换为和设备表一致的格式，简化后续关联逻辑，避免重复计算函数。
• 简化分组逻辑：ONMARC的汇总无需按Cost_Element分组，WHERE条件已限定唯一值，减少不必要的分组维度。

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