MySQL递归CTE报‘表不存在’错误：统计每日有效订阅用户数及套餐信息

错误原因分析 #

• 关联表时已给subscriptions起别名t，但SELECT语句中错误引用了原表名subscriptions.plan_name，导致字段无法识别
• 未按套餐名称分组，无法实现“不同套餐的订阅用户数”统计需求
• MySQL递归CTE默认递归深度为1000，若日期范围超过1000天会触发额外报错

修正后的SQL代码 #

-- 若日期范围超过1000天，需设置递归深度（按需调整数值）
SET max_recursion_depth = 10000;

SET @start = (SELECT MIN(start_date) FROM subscriptions);
SET @end = (SELECT MAX(end_date) FROM subscriptions);

WITH cte AS (
    SELECT @start dt
    UNION ALL
    SELECT DATE_ADD(dt, INTERVAL 1 DAY) FROM cte
    WHERE dt < @end
)
SELECT
    cte.dt AS 统计日期,
    COALESCE(t.plan_name, '无有效订阅') AS 套餐名称,
    COUNT(DISTINCT t.user_id) AS 有效用户数 -- 若需统计唯一用户用COUNT(DISTINCT)；仅统计订阅记录数用COUNT(t.plan_name)
FROM cte
LEFT JOIN subscriptions t 
    ON cte.dt BETWEEN t.start_date AND t.end_date
GROUP BY cte.dt, t.plan_name
ORDER BY cte.dt, t.plan_name;

代码说明 #

1. 递归生成日期序列：从订阅最早开始日期到最晚结束日期，生成每日的日期记录
2. 关联订阅数据：通过日期匹配，找出当日处于订阅周期内的所有用户及对应套餐
3. 分组统计：按日期和套餐名称分组，统计每个日期下各套餐的有效用户数（用COUNT(DISTINCT user_id)确保同一用户不重复统计，若表中无user_id字段，可替换为COUNT(t.plan_name)）
4. 空值处理：用COALESCE将无有效订阅的情况显示为“无有效订阅”，避免结果出现NULL

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