选择第一个匹配列

Question

我有多个列的数据

col_a        | col_b            | col_c
-----------------------------------------------
good car     | medium sandwitch | good computer
bad computer | good wall        | bad wall

，我想选择第一个以&quot开头的值;good";从这三列

result
---------
good car
good wall

Answer 1

您可以使用一个简单的case表达式:

select case
when col_a like 'good%' then col_a
when col_b like 'good%' then col_b
when col_c like 'good%' then col_c
end result
from table

这将按顺序求值，因此按您希望检查的顺序排列case语句中的列。

<标题>

——编辑——要删除没有结果的行，我们有几个选项:

使用where子句

将其移动到子查询(或CTE)中

select *
from
(
select case
when col_a like 'good%' then col_a
when col_b like 'good%' then col_b
when col_c like 'good%' then col_c
end result
from table
) a
where a.result is not null

检查where子句中所有的

select case
when col_a like 'good%' then col_a
when col_b like 'good%' then col_b
when col_c like 'good%' then col_c
end result
from table
where col_a like 'good%'
or col_b like 'good%'
or col_c like 'good%'