<p>我有一個包含用戶ID、城市和預訂日期的表格。我需要找出在一個日期範圍內只訪問了一個城市而沒有訪問多個城市的用戶。 </p> <pre class="brush:php;toolbar:false;">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</pre> <table class="s-table"> <thead> <tr> <th>user_id</th> <th>city</th> <th>result</th> </tr> </thead> <tbody> <tr> <td>10</td> <td>Munich</td> <td>1</td> </tr> <tr> <td>11</td> <td>Barcelona</td> <td>2</td> </tr> <tr> <td>11</td> <td>Berlin</td> <td>1</td> </tr> <tr> <td>12</td> <td>Barcelona</td> <td>1</td> </tr> <tr> <td>13</td> <td>Prague</td> <td>2</td> </tr> <tr> <td>11</td> <td>Barcelona</td> <td>1</td> </tr> <tr> <td>13</td> <td>Berlin</td> <td>1</td> </tr> </tbody> </table> <p>用戶ID 10和12只造訪了一個城市。例如，使用者ID 11訪問了巴塞隆納兩次。 </p><p>我試過使用NOT EXISTING和INNER JOIN。如何取得只造訪了一個城市的使用者？ </p><p><br /></p>

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

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