SQL集合差异

Question

如何在SQL中执行以下操作：

select distinct(territory_id) from main_territorypricing

"minus"

select distinct(territory_id) from main_territorypricing where store_url is not null

基本上，我希望包含在第一行中的所有territory_id不包含在第二行中。

Answer 1

如果你想按你说的做：

select distinct territory_id 
from main_territorypricing
where territory_id not in (
    select territory_id from main_territorypricing where store_url is not null
    )

但根据您所描述的逻辑，更简单的解决方案是：

select territory_id 
    from main_territorypricing
    where store_url is null

假设您只有这两个字段(territory_id和store_url)，并且行是唯一的。

否则，还有另一个棘手的解决方案：

select territory_id 
from main_territorypricing
group by territory_id 
having (max(store_url) is null)

Answer 2

select distinct(territory_id) from main_territorypricing
where
    territory_id in
      (select territory_id from main_territorypricing)
and territory_id not in 
    (select territory_id from main_territorypricing where store_url is not null)