我需要同时搜索from_afrom_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

09-08 06:03