如何在SQL中使用CASE WHEN实现无查询结果时返回默认值0？

阿华AIGC实验室

2026-5-21

解决SQL无查询结果时返回默认值0的问题

首先，咱们先拆解下你当前查询的问题：当sysdate对应的日期在表中没有匹配记录时，子查询a2会返回空结果集，外层的SUM()函数就会得到NULL，而且整个查询会返回空行（没有任何数据），这显然不是你想要的效果。

下面给你两种实用的解决方案，同时解答你关于用CASE WHEN实现的疑问：

方案1：用`COALESCE`+`RIGHT JOIN`确保返回默认值

这种方法简洁高效，适配你的场景。我们通过RIGHT JOIN一个单行虚拟表（Oracle里是DUAL，其他数据库比如MySQL可以用(SELECT 1) AS dummy）来保证查询至少返回一行，再用COALESCE把聚合后的NULL替换成0：

SELECT
  COALESCE(SUM(CASE WHEN a2.status = 'SUCCESS' THEN a2.a ELSE 0 END), 0) AS success,
  COALESCE(SUM(CASE WHEN a2.status = 'FAILED' THEN a2.a ELSE 0 END), 0) AS failed,
  COALESCE(SUM(CASE WHEN a2.status = 'ERROR' THEN a2.a ELSE 0 END), 0) AS error
FROM (
  SELECT a.status, COUNT(1) AS a
  FROM table1 a
  JOIN table2 b ON a.id = b.id  -- 建议用显式JOIN语法，比逗号分隔更清晰易读
  WHERE a.date = SYSDATE
  GROUP BY a.status
) a2
RIGHT JOIN DUAL ON 1=1;  -- 关键行：确保即使a2为空，也能返回一行基础结果

方案2：拆分统计查询+`COALESCE`

如果你的场景允许，也可以把每个状态的统计拆成独立子查询，直接从DUAL查询，逻辑会更直观：

SELECT
  COALESCE(
    (SELECT COUNT(1) FROM table1 a JOIN table2 b ON a.id = b.id WHERE a.date = SYSDATE AND a.status = 'SUCCESS'),
    0
  ) AS success,
  COALESCE(
    (SELECT COUNT(1) FROM table1 a JOIN table2 b ON a.id = b.id WHERE a.date = SYSDATE AND a.status = 'FAILED'),
    0
  ) AS failed,
  COALESCE(
    (SELECT COUNT(1) FROM table1 a JOIN table2 b ON a.id = b.id WHERE a.date = SYSDATE AND a.status = 'ERROR'),
    0
  ) AS error
FROM DUAL;

用`CASE WHEN`实现的方式

如果你一定要用CASE WHEN来处理NULL转0的逻辑，写法会稍显繁琐，但原理一致：先确保查询返回一行，再用CASE WHEN判断聚合结果是否为NULL，是则返回0，否则返回原值：

SELECT
  CASE
    WHEN SUM(CASE WHEN a2.status = 'SUCCESS' THEN a2.a ELSE 0 END) IS NULL THEN 0
    ELSE SUM(CASE WHEN a2.status = 'SUCCESS' THEN a2.a ELSE 0 END)
  END AS success,
  CASE
    WHEN SUM(CASE WHEN a2.status = 'FAILED' THEN a2.a ELSE 0 END) IS NULL THEN 0
    ELSE SUM(CASE WHEN a2.status = 'FAILED' THEN a2.a ELSE 0 END)
  END AS failed,
  CASE
    WHEN SUM(CASE WHEN a2.status = 'ERROR' THEN a2.a ELSE 0 END) IS NULL THEN 0
    ELSE SUM(CASE WHEN a2.status = 'ERROR' THEN a2.a ELSE 0 END)
  END AS error
FROM (
  SELECT a.status, COUNT(1) AS a
  FROM table1 a
  JOIN table2 b ON a.id = b.id
  WHERE a.date = SYSDATE
  GROUP BY a.status
) a2
RIGHT JOIN DUAL ON 1=1;