SQL Server中含子查询的CASE语句问题咨询及SQL排查

阿华AIGC实验室

2026-5-21

排查并优化你的SQL Server查询

嘿，我来帮你梳理下这个SQL查询的问题，然后给出优化方案。首先先把你提供的原查询整理出来（注意到有拼写错误和未完成的WHERE子句，先标注出来）：

-- 原查询（存在语法错误和逻辑问题）
SELECT 
    CASE 
        WHEN (SELECT COUNT(COL1) FROM FACT_ACTIVITY GROUP BY FACT_ACTIVITY.COL2) > 1 THEN (SELECT COUNT(COL3) FROM FACT_ACTIVITY) 
        WHEN (SELECT COUNT(COL1) FROM FACT_ACTIVITY GROUP BY FACT_ACTIVITY.COL2) = 1 THEN (SELECT COUNT(COL4) FROM FACT_ACTTIVITY) -- 这里表名拼写错误：FACT_ACTTIVITY → FACT_ACTIVITY
    END,
    EXTRACT(YEAR FROM fa.INC_START_DATETIME) AS YearOFF,
    COUNT(fa.COL2) AS OffCount 
FROM FACT_ACTIVITY fa 
INNER JOIN DIM_OFF_TYPE do ON fa.OFF_TYPE_ID = do.OFF_TYPE_ID 
WHERE fa.OFF_TYPE_ID IN (24001) 
  AND YEAR(f... -- WHERE子句未完成

原查询的核心问题

咱们先拆解下原查询里的坑：

子查询返回多行导致报错：(SELECT COUNT(COL1) FROM FACT_ACTIVITY GROUP BY COL2)会返回每个COL2分组的COUNT值（多行），直接和>1或=1比较会触发“子查询返回的值不止一个”的错误，这是最致命的问题。
重复扫描表效率极低：CASE里的4个子查询都会单独扫描一次FACT_ACTIVITY表，而且没有利用主查询的OFF_TYPE_ID IN (24001)过滤条件，完全是全表扫描，性能拉胯。
语法错误：第二个WHEN里的表名写成了FACT_ACTTIVITY，少了一个字母I，会导致表不存在的错误。
聚合查询缺少GROUP BY：主查询里SELECT了YearOFF（非聚合列）和OffCount（聚合列），但没有对应的GROUP BY子句，在SQL Server默认设置下会直接报错。

优化后的查询方案

假设你的需求是：针对符合OFF_TYPE_ID=24001的数据，判断是否存在按COL2分组后COUNT(COL1)大于1的分组；如果存在，返回该范围内COL3的总数；否则返回COL4的总数，同时按年份统计COL2的数量。我把查询优化成这样：

WITH ActivityStats AS (
    -- 提前计算所需的统计值，只扫描一次符合条件的数据
    SELECT 
        -- 判断是否存在COL2分组的COUNT(COL1) > 1
        CASE WHEN EXISTS (
            SELECT 1 
            FROM FACT_ACTIVITY 
            WHERE OFF_TYPE_ID IN (24001)
            GROUP BY COL2 
            HAVING COUNT(COL1) > 1
        ) THEN 1 ELSE 0 END AS HasLargeGroup,
        -- 统计符合条件的COL3非空行数
        COUNT(COL3) AS TotalCol3,
        -- 统计符合条件的COL4非空行数
        COUNT(COL4) AS TotalCol4
    FROM FACT_ACTIVITY
    WHERE OFF_TYPE_ID IN (24001)
)
SELECT 
    -- 根据统计结果返回对应计数
    CASE 
        WHEN stats.HasLargeGroup = 1 THEN stats.TotalCol3
        ELSE stats.TotalCol4
    END AS CalculatedCount,
    YEAR(fa.INC_START_DATETIME) AS YearOFF, -- SQL Server用YEAR()更简洁
    COUNT(fa.COL2) AS OffCount
FROM FACT_ACTIVITY fa
INNER JOIN DIM_OFF_TYPE do ON fa.OFF_TYPE_ID = do.OFF_TYPE_ID
-- 关联提前计算好的统计结果
CROSS JOIN ActivityStats stats
WHERE fa.OFF_TYPE_ID IN (24001)
-- 按年份分组，符合聚合查询规则
GROUP BY YEAR(fa.INC_START_DATETIME), stats.CalculatedCount;