sql - 如何在 WHERE 和 HAVING 中查询同一列

Question

我的表有 [user_id, city_id]。

我想找出满足以下条件的用户:
- 他们至少存在于N个城市
- 它们至少出现在一组给定的城市中

由于显而易见的原因，我们不能

select user_id 
from my_table 
where city_id in ('A', 'B') 
group by user_id 
having count(city_id) > 3;

如果我使用 INTERSECT 查询，这会起作用:

(select user_id from my_table where city_id in ('A', 'B')) 
INTERSECT 
(select user_id from my_table group by user_id having count(city_id) > 3); 

(可以使用JOIN子句构造类似的查询)

然而，这两种解决方案都让我觉得不够优雅，我认为应该有一些我缺少的巧妙解决方案？
任何帮助将不胜感激。

Answer 1

您可以通过聚合和 having 来做到这一点。这是一种方法:

select user_id
from my_table
group by user_id
having count(*) > 3 and
       count(*) filter (where city_id in ('A', 'B')) = 2;

这假设用户/城市对是唯一的。如果没有，您可以使用 count(distinct):

select user_id
from my_table
group by user_id
having count(distinct city_id) > 3 and
       count(distinct city_id) filter (where city_id in ('A', 'B')) = 2;

以上假定您同时需要 'A' 和 'B'。如果您至少想要其中之一:

select user_id
from my_table
group by user_id
having count(*) > 3 and
       count(*) filter (where city_id in ('A', 'B')) >= 1;