MySQL双表生成累计余额台账的查询问题求解

问题说明 #

用UNION语句生成员工Clark的收支台账时，Balance列仅显示单行收支数值，无法实现累计余额计算。期望Balance列按规则生成：每行余额 = 前一行余额 + 当前行（Paid Amount - Received Amount）。

原查询语句：

SELECT
   a_date as `Date`,
   amount as `Paid Amount`,
   0 as `Received Amount`,
   amount - 0 as `Balance`
FROM `Table A`
WHERE name = 'Clark'
UNION
SELECT 
   b_date as `Date`,
   0 as `Paid Amount`,
   amount as `Received Amount`,
   0 - amount as `Balance`
FROM `Table B`
WHERE name = 'Clark' AND status = 'Paid' AND type = 'Loan'
ORDER BY `Date` ASC

涉及表结构及数据 #

Table A

Table B

正确SQL方案 #

利用MySQL窗口函数SUM() OVER()实现累计计算，先合并两张表的有效数据，再基于合并结果生成累计余额：

SELECT
    `Date`,
    `Paid Amount`,
    `Received Amount`,
    SUM(`Paid Amount` - `Received Amount`) OVER (ORDER BY `Date` ASC) AS `Balance`
FROM (
    SELECT
        a_date as `Date`,
        amount as `Paid Amount`,
        0 as `Received Amount`
    FROM `Table A`
    WHERE name = 'Clark'
    UNION ALL
    SELECT 
        b_date as `Date`,
        0 as `Paid Amount`,
        amount as `Received Amount`
    FROM `Table B`
    WHERE name = 'Clark' AND status = 'Paid' AND type = 'Loan'
) AS combined_data
ORDER BY `Date` ASC;

关键细节 #

1. 用UNION ALL替代UNION：目标数据无重复，UNION ALL避免不必要的去重，提升查询性能。
2. 窗口函数SUM() OVER(ORDER BY Date ASC)：按日期升序，对每行的收支差值进行累加，直接生成累计余额。

预期查询结果 #

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