mysql不使用窗口函数，怎么按月份统计当前用户连续三个月以上的使用记录

在MySQL中，若不直接使用窗口函数，可以结合子查询和自连接来实现按月份统计连续三个月以上使用记录。假设有一个名为`user_usage`的表，其中包含`user_id`（用户ID）和`usage_date`（使用日期）字段。我们可以这样操作：

1. **获取每个用户的最近一个月的使用日期**

   SELECT user_id, MAX(usage_date) as latest_usage
   FROM user_usage
   GROUP BY user_id, YEAR(MONTH(usage_date)), DAY(usage_date)

2. **找出这些用户的上个月使用记录**

   SELECT u1.user_id, u1.usage_date as prev_usage
   FROM user_usage u1
   JOIN (
       SELECT user_id, latest_usage
       FROM (
           SELECT user_id, MAX(usage_date) as latest_usage
           FROM user_usage
           GROUP BY user_id, YEAR(MONTH(usage_date)), DAY(usage_date)
       ) t1
   ) t2 ON DATEDIFF(u1.usage_date, t2.latest_usage) = 1

3. **再次自连接以找出连续使用的记录**

   SELECT a.user_id, a.prev_usage, b.prev_usage as consecutive_usage
   FROM (
       SELECT * 
       FROM (
           SELECT u1.user_id, u1.usage_date as prev_usage
           FROM user_usage u1
           JOIN (
               SELECT user_id, latest_usage
               FROM (
                   SELECT user_id, MAX(usage_date) as latest_usage
                   FROM user_usage
                   GROUP BY user_id, YEAR(MONTH(usage_date)), DAY(usage_date)
               ) t1
           ) t2 ON DATEDIFF(u1.usage_date, t2.latest_usage) = 1
       ) a
   ) a
   LEFT JOIN (
       SELECT user_id, usage_date as prev_usage
       FROM user_usage
       JOIN (
           SELECT user_id, latest_usage
           FROM (
               SELECT user_id, MAX(usage_date) as latest_usage
               FROM user_usage
               GROUP BY user_id, YEAR(MONTH(usage_date)), DAY(usage_date)
           ) t1
       ) t2 ON DATEDIFF(u1.usage_date, t2.latest_usage) = 1
   ) b ON a.user_id = b.user_id AND a.prev_usage + INTERVAL 1 MONTH = b.prev_usage

请注意，这个查询假设`usage_date`是日期类型，并且每个月的第一天被视为月初。实际应用时可能需要调整DATE_FORMAT或MONTHLY_DATE_FORMAT函数来匹配你的日期格式。