postgresql - psql中选择满足某组条件的行

Question

给出下表:

 id  |  value 
  ---+---------
  1  |    1 
  1  |    0
  1  |    3
  2  |    1
  2  |    3
  2  |    5
  3  |    2 
  3  |    1
  3  |    0
  3  |    1

我想要下表:

 id  |  value 
  ---+---------
  1  |    1 
  1  |    0
  1  |    3
  3  |    2 
  3  |    1
  3  |    0
  3  |    1

该表包含具有最小值 0 的id。我已经尝试使用 exist 和 having 但没有成功。

Answer 1

试试这个:

select * from foo where id in (SELECT id FROM foo GROUP BY id HAVING MIN(value) = 0) 

或者那个(有窗口函数)

select * from 
 (select *,min(value) over (PARTITION BY id) min_by_id from foo) a 
where min_by_id=0