配置单元SQL逻辑-case when语句

Question

我的查询：

case 
when upper(a.camp_name) like "%Event%" and (upper(a.camp_name) not like "%Event-WBR%" or upper(a.camp_name) not like "%Event-Webinar%") THEN "Field"
else "Demand"
end as Tactic

期望输出：

Event-WBR = Demand
Event = Field

实际输出：

Event-WBR = Field
Event = Field

Answer 1

实际上，您应该将'或'替换为'和'

case 
when upper(a.camp_name) like "%Event%" and (upper(a.camp_name) not like "%Event-WBR%" and upper(a.camp_name) not like "%Event-Webinar%") THEN "Demand"
else "Field"
end as Tactic

但这里还有另外两个选择：

如果你只想为"事件"这个词做，你可以做一些类似的事情

case 
when upper(a.camp_name) like "%Event" and (upper(a.camp_name) not like "%Event-WBR%" or upper(a.camp_name) not like "%Event-Webinar%") THEN "Demand"
else "Field"
end as Tactic

如果你只想排除这两个单词，你可以这样做。

case 
when upper(a.camp_name) like "%Event-WBR%" or (upper(a.camp_name) like "%Event-Webinar%" THEN "Field"
else "Demand"
end as Tactic

Answer 2

您可以使用两个when：

(case when (upper(a.camp_name) not like "%Event-WBR%" or 
upper(a.camp_name) not like "%Event-Webinar%"
) then 'Event'
when upper(a.camp_name) like "%Event%" then 'Event'
else 'Deman'
end) as tactic