需要在WHERE子句中满足两个条件的SQL

我有鸟类观察表。这是一个例子:

Unique_ID  List_ID  Locality    Common_name
A1         001      Park        Downy Woodpecker
A2         001      Park        Hairy Woodpecker
A3         001      Park        Carolina Chickadee
B1         002      Campground  Blue Jay
B2         002      Campground  Hairy Woodpecker
C1         003      Backyard    Downy Woodpecker
C2         003      Backyard    American Goldfinch
D1         004      School      American Goldfinch
D2         004      School      Hairy Woodpecker
E1         005      Park        Downy Woodpecker
E2         005      Park        Carolina Chickadee

我正在尝试为PostgreSQL写一个查询,该查询将仅返回同时出现啄木鸟,Downy和Hairy的Localities。在小示例表中,那只是公园。其他地方只有一个或另一个物种。

我试过了

SELECT List_ID, LOCALITY, COMMON_NAME FROM table
WHERE COMMON_NAME = 'Downy Woodpecker' and COMMON_NAME = 'Hairy Woodpecker';

But returned no results. My table has 1,000s of observations, it's based on eBird data and those two species are generally common throughout the country, so there has to be at least one list_ID where they occur at the same time. In my example table, only Park (based on list_ID 001) meets the condition for what I'm looking for.

If I understand IN, it will return a row that meets either. Any of the example list_IDs would work for that query, but that's not what I want. How do I write a query that forces the WHERE to meet multiple conditions?

评论
  • 甜小妞
    甜小妞 回复

    First filter the table, group by Locality and return only the localities containing both kinds:

    select Locality
    from tablename
    where Common_name in ('Downy Woodpecker', 'Hairy Woodpecker')
    group by Locality
    having count(distinct Common_name) = 2
    
  • 爱拉乌由
    爱拉乌由 回复

    您可以首先编写一个查询,该查询返回“拥有”两种啄木鸟的位置的信息。 (按位置分组,并在having子句中写一个条件,该条件将返回位置)

    然后,您可以使用该查询为所需的位置过滤基表。