sql - 如果存在匹配项，则仅选择包含 COLUMN= 的行，否则选择 COLUMN IS NULL

Question

下面是演示问题的示例脚本:

CREATE TABLE person (
  id NUMERIC PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  city VARCHAR(255)
);

INSERT INTO person(id, name, city) VALUES(1, 'John', 'New York');
INSERT INTO person(id, name, city) VALUES(2, 'Mike', 'Boston');
INSERT INTO person(id, name, city) VALUES(3, 'Ralph', NULL);

对于city='Boston'，我希望仅返回第二行。对于 city='Chicago'，我希望返回第 3 行。

Answer 1

如果您要查找一行:

select p.*
from person p
where city is null or city = 'Boston'
order by (city = 'value') desc
fetch first 1 row only;

如果你可以有多场比赛，那么我建议:

select p.*
from person p
where p.city = 'Boston'
union all
select p.*
from person p
where p.city is null and
      not exists (select 1 from person p2 where p2.city = 'Boston');

或者，使用窗口函数:

select p.*
from (select p.*, count(*) filter (where p.city = 'Boston') as cnt
      from person p
     ) p
where (cnt > 0 and p.city = 'Boston') or
      (cnt = 0 and p.city is null);