sql - 如何在Oracle中查找相关值？

Question

假设您有一个表，例如:

id   foreign_key  status
------------------------
1    1            new
2    1            incative
3    1            approved
4    2            new
5    2            new
6    2            approved
7    3            new
8    3            approved
9    4            approved

如何查找对于给定的foreign_key只有一条记录处于新状态而其他记录已批准的记录，就像foreign_key 3的情况一样？

Answer 1

select foreign_key from table
group by foreign_key
having 
   abs(1 - count(case status when 'new' then 1 end)) + 
   abs(count(1) - 1 - count(case status when 'approved' then 1 end)) = 0