MySQL带变量查询正常，移除筛选条件后返回结果异常

你遇到的核心问题是：当移除年份和团队的筛选变量后，原查询的关联逻辑和分组方式完全适配单一维度筛选的场景，没法支持全量数据的跨年份、跨团队聚合——原查询里的RIGHT JOIN条件硬绑定了特定年份和团队，导致全量查询时无法正确关联所有年份的月份数据，自然也就没法从数据集最早的1985年开始展示结果。

原查询的核心问题点 #

• 原查询的RIGHT JOIN条件里固定了r.theYear = (select @theYear)和r.team = (select @team)，去掉变量后这部分条件失效，无法关联所有年份的月份数据；
• 子查询的分组和关联逻辑只针对单一年份设计，全量查询时没有按年份+团队维度隔离，会出现跨年份、跨团队的错误聚合。

修改后的适配全量数据的查询语句 #

下面的查询会按年份+团队+月份维度计算累积员工数，并且确保每个年份的所有12个月都能展示（即使该月没有入职/离职数据）：

SELECT
    t1.growth,
    SUM(t2.growth) AS Emp_Count,
    t1.theYear,
    t1.team,
    t1.location,
    t1.division,
    t1.status,
    CASE 
        WHEN t1.Month = 1 THEN 'JAN' 
        WHEN t1.Month = 2 THEN 'FEB' 
        WHEN t1.Month = 3 THEN 'MAR' 
        WHEN t1.Month = 4 THEN 'APR' 
        WHEN t1.Month = 5 THEN 'MAY' 
        WHEN t1.Month = 6 THEN 'JUN' 
        WHEN t1.Month = 7 THEN 'JUL' 
        WHEN t1.Month = 8 THEN 'AUG' 
        WHEN t1.Month = 9 THEN 'SEP' 
        WHEN t1.Month = 10 THEN 'OCT' 
        WHEN t1.Month = 11 THEN 'NOV' 
        WHEN t1.Month = 12 THEN 'DEC' 
    END AS myMONTH
FROM (
    -- 生成所有年份+团队+月份的全量组合，左关联原数据填充字段
    SELECT
        COALESCE(r.growth, 0) AS growth,
        y.theYear,
        tm.team,
        COALESCE(r.division, 'Unknown') AS division,
        COALESCE(r.location, '') AS location,
        COALESCE(r.status, 'ACTIVE') AS status,
        m.mon_num AS Month
    FROM (SELECT DISTINCT theYear FROM Reports) y
    CROSS JOIN (SELECT DISTINCT team FROM Reports) tm
    CROSS JOIN (
        SELECT 1 mon_num UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 
        UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 
        UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12
    ) m
    LEFT JOIN Reports r 
        ON r.theYear = y.theYear 
        AND r.team = tm.team 
        AND r.themonth = m.mon_num
) AS t1
JOIN (
    -- 同样生成全量组合，用于累积计算
    SELECT
        COALESCE(r2.growth, 0) AS growth,
        y.theYear,
        tm.team,
        m.mon_num AS Month
    FROM (SELECT DISTINCT theYear FROM Reports) y
    CROSS JOIN (SELECT DISTINCT team FROM Reports) tm
    CROSS JOIN (
        SELECT 1 mon_num UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 
        UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 
        UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12
    ) m
    LEFT JOIN Reports r2 
        ON r2.theYear = y.theYear 
        AND r2.team = tm.team 
        AND r2.themonth = m.mon_num
) AS t2 
    ON t1.theYear = t2.theYear 
    AND t1.team = t2.team 
    AND t1.Month >= t2.Month
GROUP BY t1.theYear, t1.team, t1.Month, t1.growth, t1.location, t1.division, t1.status
ORDER BY t1.theYear ASC, t1.team ASC, t1.Month ASC;

关键修改说明 #

1. 全量维度组合生成：用CROSS JOIN生成所有存在的年份、团队与12个月的笛卡尔积，确保每个年份的每个月份都有记录，即使该月没有数据；
2. 关联条件调整：把原查询绑定变量的关联逻辑，改为按年份+团队+月份左关联，适配全量数据场景；
3. 累积计算维度隔离：在t1和t2的关联中增加theYear和team的匹配，确保累积计算是在同一年份、同一团队内按月份递增，避免跨维度错误聚合；
4. NULL值处理：用COALESCE填充NULL值，确保无数据的月份growth显示为0，其他维度字段也填充合理默认值。

修改后，查询就能正确从数据集最早的1985年开始，按年份、团队、月份展示每个月的growth和累积员工数了。

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