在MySQL中找到仅访问一个城市而不是多个城市的用户 [duplicate]

Question

我有一个包含用户ID、城市和预订日期的表格。我需要找出在一个日期范围内只访问了一个城市而没有访问多个城市的用户。

SELECT 
user_id, 
city, 
COUNT(*) as result 
FROM visits
WHERE start_ts >= 1675209600 -- 1675209600 = 01.02.2023 00:00
AND end_ts <= 1676419200 -- 1676419200 = 15.2.2023 00:00
GROUP BY user_id, city

user_id	city	result
10	Munich	1
11	Barcelona	2
11	Berlin	1
12	Barcelona	1
13	Prague	2
11	Barcelona	1
13	Berlin	1

用户ID 10和12只访问了一个城市。例如，用户ID 11访问了巴塞罗那两次。

我尝试过使用NOT EXISTING和INNER JOIN。如何获取只访问了一个城市的用户？

Answer 1

请在您的查询中添加HAVING条件：

SELECT user_id, city, COUNT(*) as result
FROM visits 
WHERE start_ts >= 1675209600 -- 1675209600 = 01.02.2023 00:00
AND end_ts <= 1676419200 -- 1676419200 = 15.2.2023 00:00
GROUP BY user_id, city
HAVING result = 1

如果您需要仅计算唯一城市的数量，您可以使用COUNT(DISTINCT(city))并将其从分组中移除，如下所示：

SELECT user_id, city, COUNT(DISTINCT(city)) as result
FROM visits 
WHERE start_ts >= 1675209600 -- 1675209600 = 01.02.2023 00:00
AND end_ts <= 1676419200 -- 1676419200 = 15.2.2023 00:00
GROUP BY user_id
HAVING result = 1