如果没有匹配到其他行，则返回默认行的MYSQL查询

Question

我有一个名为 "cities" 的示例表格，如下所示：

id   |   city
----------------
1      London
2      Liverpool
3      Birmingham
4      (Other)

如果我提供一个不存在的城市，能否编写一个查询返回(其他)，例如:

SELECT * FROM cities WHERE city = "London"      --> {1, London}
SELECT * FROM cities WHERE city = "Manchester"  --> {4, (Other) }

我理解第二个 SELECT 语句将返回空值。但是我该如何修改它，以便返回“(Other)”行？参考代码：

SELECT * FROM cities WHERE city = "Manchester" OTHERWISE WHERE city = "(Other)"

- Gowire

---

自我LEFT JOIN表格。 - jarlh

Answer 1

一个方法是：

select c.*
from cities c
where c.city in (?, '(Other)')
order by c.city = '(Other)'
limit 1;

这将检索可能匹配的两行数据（?是您想要的名称的占位符）。如果有两行数据，则选择不是“other”的那一行。

- Gordon Linoff

Answer 2

请考虑：

select * 
from cities 
where 
    city = 'Manchester'
    or (city = '(Other)' and not exists (select 1 from cities where city = 'Manchester'))

- GMB

Answer 3

select DISTINCT 'Other' AS city
FROM cities WHERE NOT EXISTS (select * from cities where city = 'Brisbane')
UNION ALL
SELECT city from cities where city = 'Brisbane'

- MikeAinOz

Answer 4

使用稍微有些变化的自连接

select a.id, a.city
from (select *, city = '(Other)' as other from cities)  a
left join cities b on b.city = 'London'
where a.city = b.city or (b.city is null and a.other = 1)

演示

- Radagast