有一个数据库表，其中包含每个用户在城市中的签到记录。我需要知道一个用户在一个城市呆了多少天，然后，一个用户对一个城市进行了多少次访问（一次访问包括在一个城市连续逗留的天数）。
所以，考虑我有下表（简化，只包含DATETIMEs - 相同的用户和城市）：
DATETIME
datetime ------------------- 2011-06-30 12:11:46 2011-07-01 13:16:34 2011-07-01 15:22:45 2011-07-01 22:35:00 2011-07-02 13:45:12 2011-08-01 00:11:45 2011-08-05 17:14:34 2011-08-05 18:11:46 2011-08-06 20:22:12
该用户到过这个城市的天数为6 ( 30.06 , 01.07 , 02.07 , 01.08 , 05.08 , 06.08 )。
我想这样做使用 SELECT COUNT(id) FROM table GROUP BY DATE(datetime)
SELECT COUNT(id) FROM table GROUP BY DATE(datetime)
然后，对于该用户对该城市的访问次数，查询应返回3 ( 30.06-02.07 , 01.08 , 05.08-06.08 )。
问题是我不知道如何构建这个查询。
任何帮助将不胜感激！
您可以通过查找前一天没有签到的签到来找到每次访问的第一天。
select count(distinct date(start_of_visit.datetime)) from checkin start_of_visit left join checkin previous_day on start_of_visit.user = previous_day.user and start_of_visit.city = previous_day.city and date(start_of_visit.datetime) - interval 1 day = date(previous_day.datetime) where previous_day.id is null
此查询有几个重要部分。
首先，每个签入都与前一天的任何签入相结合。但由于它是外部联接，如果前一天没有签入，联接的右侧将有NULL结果。该WHERE过滤发生在加盟之后，所以只保留那些签入从左侧那里有从右侧无。 LEFT OUTER JOIN/WHERE IS NULL对于发现那里的东西非常方便不是。
NULL
WHERE
LEFT OUTER JOIN/WHERE IS NULL
然后它会计算不同的签到日期，以确保如果用户在访问的第一天多次签到，它不会重复计算。（当我发现可能的错误时，我实际上在编辑时添加了该部分。）
编辑：我刚刚重新阅读了您针对第一个问题提出的查询。您的查询将获得给定日期的签到次数，而不是日期计数。我认为你想要这样的东西：
select count(distinct date(datetime)) from checkin where user='some user' and city='some city'
