我需要同时搜索from_a
和from_alt
字段,但应该只选择命中字段(与搜索条件匹配的字段)作为from_airport
示例表:
+--------+-----------------+
| from_a | from_alt |
+--------+-----------------+
| FRA | BER,MUQ |
| JFK | CAL,FRA |
+--------+-----------------+
正常选择的示例查询:
select from_a as from_airport
from details
where from_a like 'FRA%'
or from_alt like 'FRA%';
但我需要选择的是命中区域
from_airport
这个例子的结果应该是,
+--------------+
| from_airport |
+--------------+
| FRA |
| CAL,FRA |
+--------------+
对于普通的mysql查询,这可能吗?
最佳答案
使用CASE
:
select CASE
WHEN from_a like 'FRA%' THEN from_a
WHEN from_alt like 'FRA%' THEN from_alt
END as from_airport
from details
where from_a like 'FRA%' or from_alt like 'FRA%';
如果两个字段都匹配,则
from_a
优先于from_alt
。