如何修改SQL语句使同一filename生成多个不同输出值？

场景与需求 #

原始SQL语句如下：

Select Case 
  when filename like '%bbb%' then 'apple' 
  when filename like '%aaa%' then 'banan' 
  When filename like '%ccc%' then 'kiwi' 
  Else filename 
End

当前输出为：apple Banan Kiwi

需要实现的效果是：当filename匹配%bbb%时，同时输出apple和lemon两个值，最终期望输出：

apple 
Banan 
Kiwi 
Lemon

用户尝试了以下代码，但无法实现需求：

Select Case 
  when filename like '%bbb%' then 'apple' 
  when filename like '%aaa%' then 'banan' 
  When filename like '%ccc%' then 'kiwi' 
  When filename like '%bbb%' then 'lemon' 
  Else filename 
End 

原因是Case语句采用短路求值，第一个匹配%bbb%的分支会直接返回apple，后续的%bbb%分支永远不会执行。

解决方法 #

方法1：使用UNION ALL组合查询 #

通过拆分查询，用UNION ALL将多个结果合并，让%bbb%的情况生成两行结果：

-- 处理非bbb的匹配项
Select 
  Case 
    when filename like '%aaa%' then 'banan' 
    when filename like '%ccc%' then 'kiwi' 
    Else filename 
  End as result
from your_table
where filename not like '%bbb%'

UNION ALL

-- 为bbb生成apple
Select 'apple' as result
from your_table
where filename like '%bbb%'

UNION ALL

-- 为bbb生成lemon
Select 'lemon' as result
from your_table
where filename like '%bbb%'

方法2：使用CROSS JOIN关联临时值表 #

通过关联包含目标输出值的临时表，让每个%bbb%的记录对应多个输出值：

Select 
  case 
    when t.filename like '%aaa%' then 'banan'
    when t.filename like '%ccc%' then 'kiwi'
    when t.filename like '%bbb%' then v.value
    else t.filename
  end as result
from your_table t
left join (
  -- 定义bbb需要输出的两个值
  select 'apple' as value union all select 'lemon'
) v on t.filename like '%bbb%'
-- 过滤：非bbb记录只保留一行，bbb记录保留两行
where v.value is not null or t.filename not like '%bbb%'

关键说明 #

• Case语句是短路求值逻辑，只要找到第一个匹配的条件就会返回结果，无法通过多个Case分支实现同一条件输出多值。
• UNION ALL适合简单场景，直接拆分查询合并结果；Cross Join适合需要批量处理多个匹配值的场景，扩展性更强。

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