Oracle SQL多值合并行去空值：设备校准日志数据转换问题

你需要将行式存储的设备校准日志转换为列式结构，同时保留重测记录。原SQL使用MAX()分组会丢失重测的FAIL记录，因为分组后同一设备、批次下的LOW校准只会保留最大值（或最后一条），无法区分多次测试。

核心需求：

• 按设备、批次聚合三种校准类型的结果
• 保留所有重测记录（如设备2的LOW校准两次测试都要显示）
• 重测记录中，未重测的校准类型（如HIGH、ROOM TEMP）需沿用其有效结果

通过以下步骤实现：

1. 给每个设备、批次、校准类型的测试记录分配序号，标记重测次数
2. 获取每个设备、批次下各校准类型的最新有效结果，用于补全重测记录中缺失的其他校准值
3. 使用PIVOT将行式数据转成列式，同时补全缺失的校准值
4. 基于各校准类型的判定结果生成整体的结果判定

完整SQL代码 #

WITH calib_with_seq AS (
    -- 给每个设备、批次、校准类型的测试分配序号，标记重测次数
    SELECT 
        equipment_#,
        lot,
        calibration,
        num_result,
        result_assessment,
        ROW_NUMBER() OVER (PARTITION BY equipment_#, lot, calibration ORDER BY num_result) AS seq_id
        -- 若有测试时间字段，建议替换num_result为test_time排序，更准确
    FROM your_table_name
),
latest_calib AS (
    -- 获取每个设备、批次下各校准类型的最新结果（用于补全重测记录的缺失值）
    SELECT 
        equipment_#,
        lot,
        MAX(CASE WHEN calibration = 'HIGH' THEN num_result END) AS latest_high,
        MAX(CASE WHEN calibration = 'LOW' THEN num_result END) AS latest_low,
        MAX(CASE WHEN calibration = 'ROOM TEMP' THEN num_result END) AS latest_room_temp,
        MAX(CASE WHEN calibration = 'HIGH' THEN result_assessment END) AS latest_high_assess,
        MAX(CASE WHEN calibration = 'LOW' THEN result_assessment END) AS latest_low_assess,
        MAX(CASE WHEN calibration = 'ROOM TEMP' THEN result_assessment END) AS latest_room_assess
    FROM calib_with_seq
    GROUP BY equipment_#, lot
),
pivoted_data AS (
    -- 行转列，同时补全重测记录中缺失的校准值
    SELECT 
        equipment_#,
        lot,
        seq_id,
        NVL(HIGH, latest_high) AS "HIGH校准值（CALIB HIGH）",
        NVL(LOW, latest_low) AS "LOW校准值（CALIBR LOW）",
        NVL("ROOM TEMP", latest_room_temp) AS "室温校准值（ROOM TEMP）",
        -- 补全各校准类型的判定结果
        NVL(MAX(CASE WHEN calibration = 'HIGH' THEN result_assessment END), latest_high_assess) AS high_assess,
        NVL(MAX(CASE WHEN calibration = 'LOW' THEN result_assessment END), latest_low_assess) AS low_assess,
        NVL(MAX(CASE WHEN calibration = 'ROOM TEMP' THEN result_assessment END), latest_room_assess) AS room_assess
    FROM calib_with_seq
    JOIN latest_calib USING (equipment_#, lot)
    PIVOT (
        MAX(num_result) FOR calibration IN ('HIGH' AS HIGH, 'LOW' AS LOW, 'ROOM TEMP' AS "ROOM TEMP")
    )
    GROUP BY equipment_#, lot, seq_id, latest_high, latest_low, latest_room_temp, latest_high_assess, latest_low_assess, latest_room_assess
)
-- 最终输出，生成整体结果判定
SELECT 
    equipment_# AS "设备编号（EQUIP #）",
    COALESCE(lot, 'N/A') AS "批次（LOT）", -- 处理ROOM TEMP的NULL批次
    "HIGH校准值（CALIB HIGH）",
    "LOW校准值（CALIBR LOW）",
    "室温校准值（ROOM TEMP）",
    CASE 
        WHEN high_assess = 'FAIL' OR low_assess = 'FAIL' OR room_assess = 'FAIL'
        THEN 'FAIL'
        ELSE 'PASS'
    END AS "结果判定（RESULT ASSESS）"
FROM pivoted_data
ORDER BY equipment_#, seq_id;

代码说明 #

• calib_with_seq：使用ROW_NUMBER()给同一设备、批次、校准类型的测试按顺序编号，重测的记录会得到不同的seq_id。
• latest_calib：聚合每个设备、批次下各校准类型的最新结果，确保重测记录中未重测的校准类型能显示有效结果。
• pivoted_data：通过PIVOT将行式数据转为列式，并用NVL()补全缺失的校准值，同时保留各校准类型的判定结果。
• 最终查询：根据各校准类型的判定结果生成整体判定，只要有一个校准类型失败，整体判定为FAIL，否则为PASS。

输出验证 #

执行后将得到你期望的结果：

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