如何在雅典娜中正确使用sql 'case when‘

Question

select  
 (case "plm"."event" when ('newMessage' and plm.id == vm.id) then 'VoiceMessgae' else plm."event" end) as "event"
   FROM 
   others_messages plm
   left join voice_messages vm on plm.id = vm.id

它在输入'(case "plm"."event“when ) ('newMessage‘和plm.id ==’)时没有显示可行的替代方案

我怎么才能修复它呢？

Answer 1

为了在语法上正确，case表达式应该是：

select (case when "plm"."event" = 'newMessage' and plm.id = vm.id
             then 'VoiceMessgae'
             else plm."event"
        end) as "event" 

case提供了两种语法。如果有复杂的表达式，则需要使用搜索到的case，其中布尔表达式跟在when之后。

Answer 2

我认为你想要：

select
    case when "plm"."event" = 'newMessage' and vm.id is not nulll
        then 'VoiceMessgae' 
        else plm."event" 
    end as "event" 
from others_messages plm 
left join voice_messages vm on plm.id = vm.id

您不需要在case条件中执行相等性检查- left join已经执行了。只需确保left joined表中的id不是null (换句话说：left join中存在匹配)。

如果发现使用exists可能比使用left join更清楚地表达逻辑

select
    case when "plm"."event" = 'newMessage' and exists (select 1 from voice_messages vm where plm.id = vm.id)
        then 'VoiceMessgae' 
        else plm."event" 
    end as "event" 
from others_messages plm