MySQL子查询外层别名使用报错及交易余额计算需求

为什么会出现unknown column p.date错误？ #

你在子查询里写的where inner_p.date <= p.date会报错，是因为这个子查询是非关联子查询——它会在外部查询执行前独立运行，完全不知道外部查询里的payments p表，自然找不到p.date这个列。要让子查询能引用外部的列，得把它改成关联子查询，或者用更高效的窗口函数方案。

实现预期结果的两种方法 #

方法1：用窗口函数（MySQL 8.0+ 优先选这个！） #

MySQL 8.0及以上支持窗口函数，能直接按交易分组、按付款日期排序，计算到当前行的累计付款金额，代码简洁又高效：

SELECT
    t.number,
    DATE(p.date) AS `date`,
    ti.total AS `total`,
    -- 按交易分组，按付款日期排序，累计计算到当前行的付款金额
    SUM(p.amount) OVER (PARTITION BY p.transaction_id ORDER BY p.date) AS `paid`,
    -- 用交易总金额减去累计付款，得到当前余额
    ti.total - SUM(p.amount) OVER (PARTITION BY p.transaction_id ORDER BY p.date) AS `balance`
FROM payments p
LEFT JOIN transactions t ON p.transaction_id = t.id
LEFT JOIN (
    -- 先计算每个交易的总金额
    SELECT
        inner_ti.transaction_id,
        SUM((inner_ti.price - inner_ti.discount) * inner_ti.quantity) AS `total`
    FROM transaction_items inner_ti
    GROUP BY inner_ti.transaction_id
) ti ON t.id = ti.transaction_id
ORDER BY t.number, p.date ASC;

方法2：用关联子查询（兼容MySQL 5.x） #

如果你的MySQL版本比较旧，不支持窗口函数，就用关联子查询。这种子查询会和外部查询的每一行关联，能访问外部的p.transaction_id和p.date：

SELECT
    t.number,
    DATE(p.date) AS `date`,
    ti.total AS `total`,
    -- 关联子查询：计算当前交易中，日期<=当前付款日期的累计付款
    (
        SELECT SUM(inner_p.amount)
        FROM payments inner_p
        WHERE inner_p.transaction_id = p.transaction_id
          AND inner_p.date <= p.date
    ) AS `paid`,
    -- 计算当前余额
    ti.total - (
        SELECT SUM(inner_p.amount)
        FROM payments inner_p
        WHERE inner_p.transaction_id = p.transaction_id
          AND inner_p.date <= p.date
    ) AS `balance`
FROM payments p
LEFT JOIN transactions t ON p.transaction_id = t.id
LEFT JOIN (
    SELECT
        inner_ti.transaction_id,
        SUM((inner_ti.price - inner_ti.discount) * inner_ti.quantity) AS `total`
    FROM transaction_items inner_ti
    GROUP BY inner_ti.transaction_id
) ti ON t.id = ti.transaction_id
ORDER BY t.number, p.date ASC;

额外说明 #

你之前想按p.date分组的需求，其实不需要显式GROUP BY——上面的两种方案都是针对每一笔付款记录计算累计值，每一行就是一笔付款后的状态，完全符合你要展示每笔付款后余额的要求。如果同一天同一交易有多笔付款，代码会按日期顺序正确累计。

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