SQL按年分组统计车辆数量时COUNT查询总有一列值为0是什么原因

问题背景 #

需要统计得到3列的分组结果，列要求如下：

1. 年份
2. 进口车数量
3. 俄产车数量
   统计规则：按年份分组统计。

数据源结构 #

源表autotable共3个字段：

• year：年份
• type：车辆类型
• id application：申请id

异常现象 #

查询结果不符合预期，要么其中一列统计值为0，要么两列数值完全相同。

已尝试的SQL写法 #

-- 写法1：CASE + COUNT
SELECT autotable.year as Year, 
       count(CASE WHEN autotable.Type='Foreign' THEN 1 ELSE null END) as Quantity_Foreign_Cars, 
       count(CASE WHEN autotable.Type= 'Russia' THEN 1 ELSE null END) as Quantity_Russian_Cars 
FROM banktest.autotable 
group by autotable.Year;

-- 写法2：IF + SUM
SELECT autotable.year as Year, 
       sum(IF (autotable.Type='Foreign',1,0)) as Foreign_Cars,
       sum(IF (autotable.Type ='Russia',1,0)) as Russian_Cars 
FROM banktest.autotable 
group by autotable.Year;

-- 写法3：子查询
SELECT autotable.year as Year, 
       (SELECT count(autotable.Type) 
        FROM banktest.autotable 
        where autotable.Type='Foreign') as Foreign_Cars,
       (SELECT count(autotable.Type) 
        FROM banktest.autotable 
        having autotable.Type='Russia') as Russian_Cars 
FROM banktest.autotable 
group by autotable.Year;

问题原因分析 #

1. 前两种写法本身没有语法错误，结果异常大概率是源表Type字段的实际存储值和你写的匹配值不一致导致的，常见问题包括：枚举值大小写不匹配、值前后存在空格、存储的是其他枚举值（比如俄产车存的是RU而非Russia）、存在不可见特殊字符。
2. 第三种写法逻辑完全错误，有两个问题：
   • 子查询没有关联外层的年份条件，统计的是全表所有年份的车辆总数，所以每个年份返回的数值都相同
   • 第二个子查询误用HAVING替代WHERE，HAVING是分组后过滤条件，此处没有分组逻辑，要么返回全表计数要么返回空值。

解决方案 #

第一步：先排查Type字段实际值 #

执行以下语句查看Type字段的枚举值和对应计数，确认匹配值是否正确：

SELECT Type, COUNT(*) as cnt FROM banktest.autotable GROUP BY Type;

第二步：修正SQL（增加容错处理） #

增加去除空格、大小写统一的逻辑，避免非业务类匹配错误：

SELECT 
  year AS 年份,
  COUNT(CASE WHEN TRIM(LOWER(Type)) = 'foreign' THEN 1 END) AS 进口车数量,
  COUNT(CASE WHEN TRIM(LOWER(Type)) = 'russia' THEN 1 END) AS 俄产车数量
FROM banktest.autotable
GROUP BY year
ORDER BY year;

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