MySQL生成近7天日期关联transactions表计算逐日累计库存问题

生成近7天的每日日期记录，关联transactions表统计截止当日的quantity字段累计值stockOnDate，预期输出格式如下：

• 报错Unknown column 'tDate' in 'where clause'：SQL执行时WHERE子句优先级高于SELECT子句，SELECT中定义的别名tDate无法在同层级的WHERE条件中被识别。
• 无法引用外层v.date字段：LEFT JOIN右侧的交易表聚合查询是独立派生表，作用域和外层查询隔离，不能直接访问外层日期表的字段。

核心修改点是将JOIN关联条件从日期相等调整为小于等于，匹配每个日期对应的所有历史交易后分组求和，即可得到截止当日的累计库存，可用SQL如下：

SELECT b.date, SUM(a.quantity) AS stockOnDate
FROM (
 SELECT DATE(ADDDATE(DATE_SUB(NOW(),INTERVAL 6 DAY), t3*1000 + t2*100 + t1*10 + t0)) AS `date` 
       FROM (SELECT 0 t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
            (SELECT 0 t1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
            (SELECT 0 t2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
            (SELECT 0 t3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3
) b 
LEFT JOIN transactions a ON DATE(a.timestamp) <= b.date
WHERE b.date BETWEEN DATE(NOW()) - INTERVAL 6 DAY AND DATE(NOW())
AND a.organisationId = 1
GROUP BY b.date
ORDER BY b.date ASC

如需统计每个商品ID对应日期的库存水平，将GROUP BY条件修改为GROUP BY a.itemID, b.date即可。

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