优化含多CASE语句且基于列内容计数的SQL查询

看来你这段嵌套在CASE里的关联子查询，正是拖慢整个查询的核心问题！在数据量庞大的场景下，这种逐行执行的子查询会重复扫描{SPS_LINFARMA}表N次（N等于主表符合条件的行数），性能损耗极大。我给你几个实操性强的优化方案：

方案1：用预聚合+JOIN替代关联子查询 #

把需要统计的结果提前计算好，再和主表关联，避免重复扫描子表。这里用CTE（公共表表达式）来实现最直观：

-- 先预计算每个IdCodFact对应的去重NumRec数量
WITH linfarma_rec_stats AS (
    SELECT 
        IdCodFact,
        COUNT(DISTINCT NumRec) AS distinct_rec_count
    FROM {SPS_LINFARMA}
    GROUP BY IdCodFact
)
SELECT 
    -- 保留你原来的其他字段
    f.IdCodFact,
    f.TipoFactura,
    f.IsPurged,
    -- 用JOIN后的字段替换原来的嵌套子查询
    CASE 
        WHEN f.TipoFactura = 'F' AND f.IsPurged = 0 THEN lrs.distinct_rec_count
        WHEN f.TipoFactura = 'F' AND f.IsPurged = 1 THEN 
            -- 这里你原来的语句没写完，如果是类似的统计，建议也放到预聚合CTE里
            -- 比如如果是统计另一个维度，可在CTE中新增字段
        -- 其他CASE分支
    END AS your_target_column
FROM {SPS_FACTURAS} f
LEFT JOIN linfarma_rec_stats lrs 
    ON f.IdCodFact = lrs.IdCodFact

这个方法的优势是：只扫描一次{SPS_LINFARMA}表，把统计结果缓存到CTE中，再和主表做一次关联查询，相比原来的逐行子查询，性能提升非常明显，数据量越大效果越显著。

方案2：给关键字段加索引，加速统计和关联 #

索引是大表性能优化的核心，针对你的场景建议加两个索引：

1. 给{SPS_LINFARMA}建联合索引，直接覆盖统计需求：

CREATE INDEX IX_SPS_LINFARMA_IdCodFact_NumRec 
ON {SPS_LINFARMA}(IdCodFact, NumRec);

这个索引可以让COUNT(DISTINCT NumRec)直接从索引中获取数据，不需要扫描全表，预聚合的速度会大幅提升。

2. 给{SPS_FACTURAS}建复合索引，加速CASE中的条件判断：

CREATE INDEX IX_SPS_FACTURAS_TipoFactura_IsPurged 
ON {SPS_FACTURAS}(TipoFactura, IsPurged);

如果你的查询中还有WHERE条件，建议把过滤字段也加到这个索引里，进一步减少需要处理的数据行数。

方案3：提前过滤冗余数据 #

如果你的主表{SPS_FACTURAS}中只有TipoFactura='F'的记录才需要这个统计，那可以先过滤掉其他类型的记录，减少后续关联的数据量：

WITH filtered_facturas AS (
    SELECT * FROM {SPS_FACTURAS} WHERE TipoFactura = 'F'
),
linfarma_rec_stats AS (
    SELECT IdCodFact, COUNT(DISTINCT NumRec) AS distinct_rec_count
    FROM {SPS_LINFARMA}
    -- 只关联需要统计的IdCodFact，进一步缩小子表扫描范围
    WHERE IdCodFact IN (SELECT IdCodFact FROM filtered_facturas)
    GROUP BY IdCodFact
)
SELECT 
    -- 你的字段和CASE逻辑
FROM filtered_facturas f
LEFT JOIN linfarma_rec_stats lrs ON f.IdCodFact = lrs.IdCodFact

这样可以让{SPS_LINFARMA}的统计只针对主表需要的IdCodFact，避免扫描无关数据。

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