如何在Oracle单查询中合并IN与NOT IN条件的长度计算结果

方法一：条件聚合（推荐） #

通过条件聚合只需扫描一次表，就能高效计算两个结果：

SELECT 
    RJ_FSA_ID,
    ROUND(SUM(CASE WHEN SPAN_REF_NAME IN ('3x40-F-RG-ST-1L','2x40-F-G-ST-1L','1x40-F-G-ST-1L','2x40-F-RG-ST-1L','7x14MM-MICRO','1x14MM-MICRO',
    '3x40-F-G-ST-1L','2x14MM-MICRO','1x40-F-1L-RED','4x40-F-RG-ST-1L') THEN NVL(CALCULATED_LENGTH,0)/1000 ELSE 0 END), 4) AS UG_LENGTH,
    ROUND(SUM(CASE WHEN SPAN_REF_NAME NOT IN ('3x40-F-RG-ST-1L','2x40-F-G-ST-1L','1x40-F-G-ST-1L','2x40-F-RG-ST-1L','7x14MM-MICRO','1x14MM-MICRO',
    '3x40-F-G-ST-1L','2x14MM-MICRO','1x40-F-1L-RED','4x40-F-RG-ST-1L') THEN NVL(CALCULATED_LENGTH,0)/1000 ELSE 0 END), 4) AS AR_LENGTH
FROM NE.MV_SPAN@facid147
WHERE RJ_FSA_ID='MUMB1622'
GROUP BY RJ_FSA_ID;

原理：用CASE WHEN分别筛选符合IN/ NOT IN条件的行，仅对符合条件的CALCULATED_LENGTH求和，一次查询即可得到两个统计值，避免重复扫描表，性能更优。

方法二：子查询JOIN #

如果更倾向于保留原有查询逻辑，可以将两个查询作为子查询，通过RJ_FSA_ID关联合并结果：

SELECT 
    t1.RJ_FSA_ID,
    t1.UG_LENGTH,
    t2.AR_LENGTH
FROM (
    SELECT RJ_FSA_ID, ROUND(SUM(NVL(CALCULATED_LENGTH,0)/1000), 4) AS UG_LENGTH
    FROM NE.MV_SPAN@facid147
    WHERE SPAN_REF_NAME IN ('3x40-F-RG-ST-1L','2x40-F-G-ST-1L','1x40-F-G-ST-1L','2x40-F-RG-ST-1L','7x14MM-MICRO','1x14MM-MICRO',
        '3x40-F-G-ST-1L','2x14MM-MICRO','1x40-F-1L-RED','4x40-F-RG-ST-1L') 
    AND RJ_FSA_ID='MUMB1622'
    GROUP BY RJ_FSA_ID
) t1
JOIN (
    SELECT RJ_FSA_ID, ROUND(SUM(NVL(CALCULATED_LENGTH,0)/1000), 4) AS AR_LENGTH
    FROM NE.MV_SPAN@facid147
    WHERE SPAN_REF_NAME NOT IN ('3x40-F-RG-ST-1L','2x40-F-G-ST-1L','1x40-F-G-ST-1L','2x40-F-RG-ST-1L','7x14MM-MICRO','1x14MM-MICRO',
        '3x40-F-G-ST-1L','2x14MM-MICRO','1x40-F-1L-RED','4x40-F-RG-ST-1L') 
    AND RJ_FSA_ID='MUMB1622'
    GROUP BY RJ_FSA_ID
) t2 ON t1.RJ_FSA_ID = t2.RJ_FSA_ID;

原理：将两个原始查询分别作为独立子查询，通过共同的RJ_FSA_ID字段连接，合并得到包含两个长度统计的结果。这种方式逻辑直观，但会扫描两次表，数据量较大时性能不如条件聚合。

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