MySQL中关联相同日期骨架表未返回预期值问题求助

我需要用MySQL做每日维度的年度同比（YoY）营收分析，因为不是每天都有营收数据，所以要生成日期骨架表补全所有日期。现在遇到的问题是，两个相同逻辑生成的日期骨架表做LEFT JOIN后，关联字段匹配不上，gotcha列全是NULL。

日期骨架表代码 #

SELECT 
    @rownum := @rownum + 1,
    DATE_FORMAT(NOW(),'%Y-%m-01') - INTERVAL 24 MONTH + INTERVAL @rownum DAY AS revenueDay
    FROM
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8) tt1,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) tt2,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) tt3,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 4) tt4,
    (SELECT @rownum:=-1) tt0
    
    WHERE 
    DATE_FORMAT(NOW(),'%Y-%m-01') - INTERVAL 24 MONTH + INTERVAL @rownum DAY < (DATE_FORMAT(NOW(),'%Y-%m-%d') - INTERVAL 1 DAY)

日期骨架表查询结果 #

关联两个日期骨架表的代码 #

SELECT
skeleton.*,
gotcha.gotcha

FROM(
SELECT 
    @rownum := @rownum + 1,
    DATE_FORMAT(NOW(),'%Y-%m-01') - INTERVAL 24 MONTH + INTERVAL @rownum DAY AS revenueDay
    FROM
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8) tt1,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) tt2,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) tt3,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 4) tt4,
    (SELECT @rownum:=-1) tt0
    
    WHERE 
    DATE_FORMAT(NOW(),'%Y-%m-01') - INTERVAL 24 MONTH + INTERVAL @rownum DAY < (DATE_FORMAT(NOW(),'%Y-%m-%d') - INTERVAL 1 DAY)
)
skeleton

LEFT JOIN(
SELECT 
    @rownum := @rownum + 1,
    DATE_FORMAT(NOW(),'%Y-%m-01') - INTERVAL 24 MONTH + INTERVAL @rownum DAY AS revenueDay,
    'gotcha' AS gotcha
    
    FROM
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8) tt1,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) tt2,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) tt3,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 4) tt4,
    (SELECT @rownum:=-1) tt0
    
    WHERE 
    DATE_FORMAT(NOW(),'%Y-%m-01') - INTERVAL 24 MONTH + INTERVAL @rownum DAY < (DATE_FORMAT(NOW(),'%Y-%m-%d') - INTERVAL 1 DAY)
)
gotcha ON gotcha.revenueDay = skeleton.revenueDay

关联后的查询结果 #

问题原因及解决方法 #

问题出在用户变量@rownum的全局特性——MySQL的用户变量是会话级别的，第一个子查询执行后，@rownum的值已经累计到了骨架表的最大行数，第二个子查询再使用同一个@rownum时，起始值不是-1，而是第一个子查询结束后的数值，导致第二个子查询生成的revenueDay日期和第一个子查询完全不重叠，自然关联不上。

解决方法1：使用不同的用户变量 #

两个子查询分别用不同的变量，比如第一个用@rownum1，第二个用@rownum2：

SELECT
skeleton.*,
gotcha.gotcha

FROM(
SELECT 
    @rownum1 := @rownum1 + 1,
    DATE_FORMAT(NOW(),'%Y-%m-01') - INTERVAL 24 MONTH + INTERVAL @rownum1 DAY AS revenueDay
    FROM
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8) tt1,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) tt2,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) tt3,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 4) tt4,
    (SELECT @rownum1:=-1) tt0
    
    WHERE 
    DATE_FORMAT(NOW(),'%Y-%m-01') - INTERVAL 24 MONTH + INTERVAL @rownum1 DAY < (DATE_FORMAT(NOW(),'%Y-%m-%d') - INTERVAL 1 DAY)
)
skeleton

LEFT JOIN(
SELECT 
    @rownum2 := @rownum2 + 1,
    DATE_FORMAT(NOW(),'%Y-%m-01') - INTERVAL 24 MONTH + INTERVAL @rownum2 DAY AS revenueDay,
    'gotcha' AS gotcha
    
    FROM
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8) tt1,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) tt2,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) tt3,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 4) tt4,
    (SELECT @rownum2:=-1) tt0
    
    WHERE 
    DATE_FORMAT(NOW(),'%Y-%m-01') - INTERVAL 24 MONTH + INTERVAL @rownum2 DAY < (DATE_FORMAT(NOW(),'%Y-%m-%d') - INTERVAL 1 DAY)
)
gotcha ON gotcha.revenueDay = skeleton.revenueDay

解决方法2：将日期骨架表封装成CTE（MySQL 8.0+支持） #

用公共表表达式复用日期骨架表，避免重复生成和变量冲突：

WITH date_skeleton AS (
    SELECT 
        @rownum := @rownum + 1 AS row_num,
        DATE_FORMAT(NOW(),'%Y-%m-01') - INTERVAL 24 MONTH + INTERVAL @rownum DAY AS revenueDay
    FROM
        (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8) tt1,
        (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) tt2,
        (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) tt3,
        (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 4) tt4,
        (SELECT @rownum:=-1) tt0
    WHERE 
        DATE_FORMAT(NOW(),'%Y-%m-01') - INTERVAL 24 MONTH + INTERVAL @rownum DAY < (DATE_FORMAT(NOW(),'%Y-%m-%d') - INTERVAL 1 DAY)
)
SELECT
    skeleton.*,
    gotcha.gotcha
FROM date_skeleton skeleton
LEFT JOIN date_skeleton gotcha ON gotcha.revenueDay = skeleton.revenueDay;

解决方法3：重置变量（不推荐） #

在第二个子查询前手动重置@rownum，但这种方式依赖查询执行顺序，稳定性差：

SELECT
skeleton.*,
gotcha.gotcha

FROM(
SELECT 
    @rownum := @rownum + 1,
    DATE_FORMAT(NOW(),'%Y-%m-01') - INTERVAL 24 MONTH + INTERVAL @rownum DAY AS revenueDay
    FROM
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8) tt1,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) tt2,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) tt3,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 4) tt4,
    (SELECT @rownum:=-1) tt0
    
    WHERE 
    DATE_FORMAT(NOW(),'%Y-%m-01') - INTERVAL 24 MONTH + INTERVAL @rownum DAY < (DATE_FORMAT(NOW(),'%Y-%m-%d') - INTERVAL 1 DAY)
)
skeleton

LEFT JOIN(
SELECT @rownum:=-1 AS reset,
    @rownum := @rownum + 1,
    DATE_FORMAT(NOW(),'%Y-%m-01') - INTERVAL 24 MONTH + INTERVAL @rownum DAY AS revenueDay,
    'gotcha' AS gotcha
    
    FROM
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8) tt1,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) tt2,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) tt3,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 4) tt4
    
    WHERE 
    DATE_FORMAT(NOW(),'%Y-%m-01') - INTERVAL 24 MONTH + INTERVAL @rownum DAY < (DATE_FORMAT(NOW(),'%Y-%m-%d') - INTERVAL 1 DAY)
)
gotcha ON gotcha.revenueDay = skeleton.revenueDay

内容的提问来源于stack exchange，提问作者austrich.stats