如何查询用户首会话至当前月的各月会话数（含零值）

需要获取指定用户从首次会话月份到当前月份的各月会话数量，无会话的月份需显示0计数。现有session表包含session_id、user_id、start_time字段，但执行以下SQL后仅返回10个月份，无法覆盖到当前月，需修复：

SELECT DATE_FORMAT(DATE_ADD(min_month, INTERVAL m.n MONTH), '%Y-%m') as month,
       COALESCE(COUNT(c.consumption), 0) as total_consumption
FROM
  (SELECT DATE_FORMAT(MIN(start_time), '%Y-%m-01') as min_month
   FROM session
   WHERE user_id = 'sGcO42wMx1ZHXwZpZH3KWwWXzd13') as start_month
CROSS JOIN
  (SELECT n
   FROM (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
         UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) as n) as m
LEFT JOIN session as c ON DATE_FORMAT(c.start_time, '%Y-%m') = DATE_FORMAT(DATE_ADD(start_month.min_month, INTERVAL m.n MONTH), '%Y-%m')
   AND c.user_id = 'sGcO42wMx1ZHXwZpZH3KWwWXzd13'
GROUP BY month
ORDER BY month ASC;

原SQL的问题在于手动生成的数字序列仅包含0-9，最多只能生成10个月份。如果用户首次会话到当前月的间隔超过9个月，就会漏掉后续月份。以下是两种修复方案：

方案1：使用递归CTE（适用于MySQL 8.0+、PostgreSQL等支持递归的数据库） #

递归CTE会自动生成从首次会话月到当前月的所有月份，无需手动指定数量：

WITH RECURSIVE month_series AS (
    -- 起始：用户首次会话的月份
    SELECT DATE_FORMAT(MIN(start_time), '%Y-%m-01') AS month_date
    FROM session
    WHERE user_id = 'sGcO42wMx1ZHXwZpZH3KWwWXzd13'
    
    UNION ALL
    
    -- 递归生成下一个月，直到当前月
    SELECT DATE_ADD(month_date, INTERVAL 1 MONTH)
    FROM month_series
    WHERE month_date < DATE_FORMAT(NOW(), '%Y-%m-01')
)
SELECT 
    DATE_FORMAT(ms.month_date, '%Y-%m') AS month,
    COALESCE(COUNT(s.session_id), 0) AS total_sessions
FROM month_series ms
LEFT JOIN session s 
    ON DATE_FORMAT(s.start_time, '%Y-%m') = DATE_FORMAT(ms.month_date, '%Y-%m')
    AND s.user_id = 'sGcO42wMx1ZHXwZpZH3KWwWXzd13'
GROUP BY ms.month_date
ORDER BY ms.month_date ASC;

方案2：生成足够多的数字序列（兼容老版本MySQL） #

通过交叉连接生成0-99的数字序列，覆盖100个月，再过滤掉超过当前月的部分：

SELECT 
    DATE_FORMAT(DATE_ADD(min_month, INTERVAL m.n MONTH), '%Y-%m') AS month,
    COALESCE(COUNT(s.session_id), 0) AS total_sessions
FROM
    (SELECT DATE_FORMAT(MIN(start_time), '%Y-%m-01') AS min_month
     FROM session
     WHERE user_id = 'sGcO42wMx1ZHXwZpZH3KWwWXzd13') AS start_month
CROSS JOIN
    -- 生成0-99的数字，覆盖100个月
    (SELECT a.n + b.n * 10 AS n
     FROM (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
           UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
     CROSS JOIN (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
                 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b) AS m
LEFT JOIN session s 
    ON DATE_FORMAT(s.start_time, '%Y-%m') = DATE_FORMAT(DATE_ADD(start_month.min_month, INTERVAL m.n MONTH), '%Y-%m')
    AND s.user_id = 'sGcO42wMx1ZHXwZpZH3KWwWXzd13'
-- 过滤掉超过当前月的无效月份
WHERE DATE_ADD(start_month.min_month, INTERVAL m.n MONTH) <= DATE_FORMAT(NOW(), '%Y-%m-01')
GROUP BY month
ORDER BY month ASC;

内容的提问来源于stack exchange，提问作者BenjiBob