MySQL中COUNT统计nilai与t_rkpd大小关系结果异常问题

基础关联查询（可获取有效nilai和t_rkpd字段） #

SELECT wilayah,nilai,t_rkpd,tahun,periode
FROM nilai_indikator
INNER JOIN provinsi ON nilai_indikator.wilayah=provinsi.id
WHERE tahun='2022' AND periode='08' AND id_indikator='6'

统计查询（结果均为0，预期tercapai为34） #

SELECT COUNT(CASE WHEN nilai > t_rkpd THEN 1 END) AS tercapai 
FROM nilai_indikator
INNER JOIN provinsi ON nilai_indikator.wilayah=provinsi.id
WHERE tahun='2022' AND periode='08' AND id_indikator='6'

SELECT COUNT(CASE WHEN nilai < t_rkpd THEN 1 END) AS tidak_tercapai
FROM nilai_indikator
INNER JOIN provinsi ON nilai_indikator.wilayah=provinsi.id
WHERE tahun='2022' AND periode='08' AND id_indikator='6'

以下是导致统计结果全为0的常见原因及验证/解决方法：

• 数据类型不匹配
  若nilai和t_rkpd字段类型不一致（比如一个是字符串、一个是数值），直接比较会触发隐式类型转换，转换失败时返回NULL，而COUNT()会忽略NULL值，导致统计为0。

  • 验证：执行DESCRIBE nilai_indikator;（MySQL）或SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='nilai_indikator' AND COLUMN_NAME IN ('nilai','t_rkpd');查看字段类型。
  • 解决：统一类型后再比较，例如：

    COUNT(CASE WHEN CAST(nilai AS DECIMAL) > CAST(t_rkpd AS DECIMAL) THEN 1 END) AS tercapai
    

• 字段存在NULL值
  只要nilai或t_rkpd任意一个为NULL，nilai > t_rkpd的结果就是NULL，不会被COUNT()统计。

  • 验证：统计含NULL的记录数：

    SELECT COUNT(*) 
    FROM nilai_indikator
    INNER JOIN provinsi ON nilai_indikator.wilayah=provinsi.id
    WHERE tahun='2022' AND periode='08' AND id_indikator='6' 
      AND (nilai IS NULL OR t_rkpd IS NULL);
    

  • 解决：根据业务逻辑处理NULL，比如用默认值填充：

    COUNT(CASE WHEN COALESCE(nilai, 0) > COALESCE(t_rkpd, 0) THEN 1 END) AS tercapai
    

• 字符串格式不符合数值规范
  若nilai或t_rkpd是字符串类型，且包含非数字字符（比如千分位逗号"1,234"、空格、字母），隐式转换会失败返回NULL，导致比较无效。

  • 验证：查看字段实际值：

    SELECT nilai, t_rkpd 
    FROM nilai_indikator
    INNER JOIN provinsi ON nilai_indikator.wilayah=provinsi.id
    WHERE tahun='2022' AND periode='08' AND id_indikator='6'
    LIMIT 10;
    

  • 解决：清理字符串后转换类型，例如：

    COUNT(CASE WHEN CAST(REPLACE(nilai, ',', '') AS DECIMAL) > CAST(t_rkpd AS DECIMAL) THEN 1 END) AS tercapai
    

• 所有记录均为相等值
  若所有数据中nilai和t_rkpd完全相等，那么大于和小于的统计都会为0。可验证：

  SELECT COUNT(*) 
  FROM nilai_indikator
  INNER JOIN provinsi ON nilai_indikator.wilayah=provinsi.id
  WHERE tahun='2022' AND periode='08' AND id_indikator='6' 
    AND nilai = t_rkpd;
  

优化后的一次性统计查询 #

可以用一条语句同时统计三种情况，方便快速验证：

SELECT
  COUNT(CASE WHEN CAST(nilai AS DECIMAL) > CAST(t_rkpd AS DECIMAL) THEN 1 END) AS tercapai,
  COUNT(CASE WHEN CAST(nilai AS DECIMAL) < CAST(t_rkpd AS DECIMAL) THEN 1 END) AS tidak_tercapai,
  COUNT(CASE WHEN CAST(nilai AS DECIMAL) = CAST(t_rkpd AS DECIMAL) THEN 1 END) AS sama,
  COUNT(*) AS total_records
FROM nilai_indikator
INNER JOIN provinsi ON nilai_indikator.wilayah=provinsi.id
WHERE tahun='2022' AND periode='08' AND id_indikator='6';

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