如何选择在一段时间内每月工作的用户？

Question

如何修改此查询以返回给我，所有在一年的所有12个月中都工作过的用户ID？

SELECT extract(month from Date_worked) as Month,user_id FROM Users WHERE 
Date_worked >= '01/JAN/16' and Date_worked < '01/JAN/17' 

这目前返回我是这样的：

MONTH - USER_ID 
1  12 
2  12 
3  11 
3  12 
4  11 
4  12 
5  12 
6  12 
7  12 
8  12 
9  12 
10  12 
11  12 
12  12 
... 

在这种情况下，因为它与所有12个月相关的查询应该只返回USER_ID 12。

Answer 1

SELECT 
    user_id 
FROM Users 
WHERE Date_worked >= '01/JAN/16' AND Date_worked < '01/JAN/17' 
GROUP BY user_id 
HAVING COUNT(*) = 12 

如果用户能有一个给定月份相关的多条记录，然后使用以下HAVING条款计数明显月：

HAVING COUNT(DISTINCT EXTRACT(month from Date_worked)) = 12 

Answer 2

SELECT extract(month from Date_worked) as Month,user_id 
FROM Users GROUP BY user_id 
HAVING COUNT(DISTINCT extract(month from Date_worked)) = 12 

试试上面的查询。