SQL查询CONCAT不识别NULL值导致结果异常故障排查

问题根因 #

• 主流SQL方言（SQL Server、MySQL 8.0+等）的CONCAT函数默认特性：入参中存在NULL值时，会自动将NULL转换为空字符串参与拼接，而非返回整个拼接结果为NULL。例如CONCAT(NULL, ' ', 125, '%')的执行结果为' 125%'，不会返回NULL，这就是你看到仅输出百分比的核心原因。
• 内层查询未过滤Expense IS NULL的记录，导致分组统计时会生成Expense为空的分组，该分组如果被排到top1位置，拼接后就会出现异常结果。Expense为空的分组仅会占一个排名位置，所以你会看到top1异常、top2正常返回NULL的现象。

解决方案 #

方案1：调整拼接逻辑，空值直接返回NULL #

将外层查询中CONCAT部分的代码修改为加入非空判断，只要Expense为空就返回NULL：

-- 原来的写法
max(case when ranking = 1 then concat(expense,' ', expense_percent, '%') end ) top1_expense
-- 修改后的写法
max(case when ranking = 1 then case when expense is not null then concat(expense,' ', expense_percent, '%') else null end end ) top1_expense

如果你的Expense字段可能存在空字符串而非NULL，可以调整判断条件：

case when expense is not null and trim(expense) != '' then concat(expense,' ', expense_percent, '%') else null end

方案2：从根源过滤无效数据 #

在内层查询的a表中加入Expense IS NOT NULL的过滤条件，避免生成Expense为空的分组，同时和关联的b表逻辑保持统一：

select 
    a.model,
    Expense,
    count(1) Expense_count,
    total,
    round(count(1)/cast(total as float)*100,2) as expense_percent,
    row_number() OVER(partition by a.model ORDER BY count(1) DESC) as Ranking
from [EDW].[sio].[TB_R_SURVEY_IN_OPERATION] a
-- 新增过滤条件
where a.Expense is not null
join
(
    SELECT 
            Model,
            COUNT(1) total
    FROM [EDW].[sio].[TB_R_SURVEY_IN_OPERATION] 
    where Expense is not null
    group by Model
)b
on a.Model = b.Model
group by a.model, Expense, total

方案3（适配SQL Server）：改用+拼接字符串 #

SQL Server中+拼接字符串时，只要任意一个参数为NULL，整个拼接结果就为NULL，符合你的需求，注意要把数值类型的expense_percent转换为字符串：

max(case when ranking = 1 then expense + ' ' + cast(expense_percent as varchar(10)) + '%' end ) top1_expense

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