基于where条件获取特定记录

Question

select distinct country from xyz where state != "" and ((city="" and Locality="") or (city="" and Locality!="") or (city!="" and Locality="")) order by country

我需要国家列表，其中没有城市或地方，这意味着所有的城市或地方的国家是空的或空的。不要国家，如果国家的一个记录有城市或地方价值。

Answer 1

您是在寻找简单的还是:

select distinct country
from xyz
where state <> '' and
      (city = '' or Locality= '')
order by country;

如果这没有返回所需的值，则null值可能会出现问题：

select distinct country
from xyz
where state <> '' and
      (city = '' or Locality= '' or city is null or Locality is null)
order by country;

或者可能不需要state上的条件。

select country
from xyz
group by contry
having min(city = '' or Locality= '' or city is null or Locality is null) > 0
order by country;