相同条件下CASE WHEN与WHERE语句结果不同的原因排查

以下是导致两种查询结果差异的可能原因及对应的排查步骤：

1. PIVOT的自动分组逻辑导致结果分散 #

你的PIVOT查询会自动按FYEAR字段分组，最终返回每个FYEAR对应的符合条件的记录数；而WHERE查询是全局统计所有符合条件的记录总数。

• 如果4000条符合条件的记录分散在多个FYEAR中，你看到的可能是某一个FYEAR对应的[a]值为0，而非所有FYEAR的总和为0。
• 排查：去掉子查询中的FYEAR，改为全局透视统计，看结果是否与WHERE查询一致：

  SELECT * FROM(SELECT 
      CASE WHEN (DIAG_3_01 IN ('E10','E11','E12','E13','E14','O24') OR DIAG_4_01 IN ('E232','N251','P702')) AND (OPERTN_3_01 IN ('N26','P22','X07','X09','X10','X11') OR OPERTN_4_01 IN ('Q011','X215','X216','X273','X121')) THEN 'a'
          ELSE 'Other' END AS Procs,
      FCE
  FROM database
      ) AS a
      PIVOT(COUNT(FCE) FOR Procs IN ([a])) AS p;
  

2. 字段FCE的NULL值影响 #

虽然WHERE查询用COUNT(FCE)得到4000，但需确认：符合CASE WHEN条件的记录中，FCE是否均为非NULL？

• COUNT(FCE)会忽略NULL值，如果符合条件的记录中FCE全为NULL，PIVOT的COUNT(FCE)会返回0，但这种情况与你的WHERE查询结果矛盾。可以换用COUNT(*)验证：
  • 修改PIVOT中的聚合函数：

    SELECT * FROM(SELECT FYEAR, 
        CASE WHEN (DIAG_3_01 IN ('E10','E11','E12','E13','E14','O24') OR DIAG_4_01 IN ('E232','N251','P702')) AND (OPERTN_3_01 IN ('N26','P22','X07','X09','X10','X11') OR OPERTN_4_01 IN ('Q011','X215','X216','X273','X121')) THEN 'a'
            ELSE 'Other' END AS Procs
    FROM database
        ) AS a
        PIVOT(COUNT(*) FOR Procs IN ([a])) AS p;
    

  • 同时将WHERE查询改为COUNT(*)，对比结果是否一致。

3. CASE WHEN条件与WHERE条件存在隐性差异 #

仔细检查字段名拼写（比如是否把DIAG_3_01写成DIAG_3_O1、OPERTN_4_01是否拼写正确），或者数据库对NULL值的处理逻辑：

• 例如，当DIAG_3_01为NULL时，DIAG_3_01 IN (...)会返回FALSE，CASE WHEN和WHERE的处理逻辑一致，但如果字段名拼写错误，CASE WHEN的条件永远不成立，导致Procs全为Other，PIVOT的[a]列自然为0。
• 排查：单独运行子查询，筛选符合WHERE条件的记录，查看Procs字段是否有值为a的记录：

  SELECT DISTINCT Procs
  FROM(SELECT 
      CASE WHEN (DIAG_3_01 IN ('E10','E11','E12','E13','E14','O24') OR DIAG_4_01 IN ('E232','N251','P702')) AND (OPERTN_3_01 IN ('N26','P22','X07','X09','X10','X11') OR OPERTN_4_01 IN ('Q011','X215','X216','X273','X121')) THEN 'a'
          ELSE 'Other' END AS Procs
  FROM database
  WHERE (DIAG_3_01 IN ('E10','E11','E12','E13','E14','O24') OR DIAG_4_01 IN ('E232','N251','P702')) AND (OPERTN_3_01 IN ('N26','P22','X07','X09','X10','X11') OR OPERTN_4_01 IN ('Q011','X215','X216','X273','X121'))) t;
  

  如果结果中没有a，说明CASE WHEN的条件存在问题。

4. 别名的语法问题 #

你的子查询中给Procs用了单引号'Procs'作为别名，部分数据库中可能会将其识别为字符串字面量而非列名，导致PIVOT无法正确识别分组字段：

• 修改别名写法，使用方括号或去掉引号：

  SELECT * FROM(SELECT FYEAR, 
      CASE WHEN (DIAG_3_01 IN ('E10','E11','E12','E13','E14','O24') OR DIAG_4_01 IN ('E232','N251','P702')) AND (OPERTN_3_01 IN ('N26','P22','X07','X09','X10','X11') OR OPERTN_4_01 IN ('Q011','X215','X216','X273','X121')) THEN 'a'
          ELSE 'Other' END AS Procs,
      FCE
  FROM database
      ) AS a
      PIVOT(COUNT(FCE) FOR Procs IN ([a])) AS p;
  

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