sql - Postgres 通过另一列在列组中获取至少具有 1 个特定值的行

Question

我有一个sql命令:

select location,status,total from my_table

这个返回:

location - status - total
city a - active - 3
city a - pending - 2
city a - cancel - 4
city b - pending - 4
city b - cancel - 4
city c - active - 2
city c - cancel - 6

我怎样才能只返回至少有一次状态活跃且总数 >= 1 的城市的行？

所以，我想返回:

    location - status - total
    city a - active - 3
    city a - pending - 2
    city a - cancel - 4
    city c - active - 2
    city c - cancel - 6

如您所见，我不想返回城市 b，因为它至少有一次状态未处于事件状态且总数 >= 1。

Answer 1

select location,status,total from my_table
where location in (
    select location from my_table
    where status = 'active'
        and total >= 1
    )