在MySQL中计算指定条件下的Running balance（滚动余额）

MySQL数据库表结构及数据 #

CREATE TABLE table_name
    (`id` int, `e_id` int, `l_id` int, `amt` int, `type` varchar(2), `date` datetime)
;
    
INSERT INTO table_name
    (`id`, `e_id`,`l_id`, `amt`, `type`, `date`)
VALUES
    (1, 1, 10, 70000, 'Dr', '2022-01-01 00:00:00'),
    (2, 1, 11,  8000, 'Dr', '2022-01-01 00:00:00'),
    (3, 1, 12, 78000, 'Cr', '2022-01-01 00:00:00'),
    (4, 2, 10, 90000, 'Dr', '2022-02-01 00:00:00'),
    (5, 2, 11, 2000, 'Dr', '2022-02-01 00:00:00'),
    (6, 2, 12, 92000, 'Cr', '2022-02-01 00:00:00'),
    (7, 3, 13, 50000, 'Cr', '2022-03-01 00:00:00'),
    (8, 3, 14, 2000, 'Cr', '2022-03-01 00:00:00'),
    (9, 3, 15, 52000, 'Dr', '2022-03-01 00:00:00')
;

原查询语句 #

select id, 
case when `type` = 'Dr' then `amt` else 0 end as Dr,
case when `type` = 'Cr' then `amt` else 0 end as Cr,
date,
sum(case when `type` = 'Dr' then -`amt` when `type` = 'Cr' then `amt` end) over(order by date rows unbounded preceding) as balance

from table_name
WHERE l_id=12
  ;

期望输出结果 #

id  |  Dr     |  Cr      |    date    | balance         
----+---------+----------+------------+--------
 1  |   70000 |    0     | 01-01-2022 | 70000
 2  |    8000 |    0     | 01-01-2022 | 78000
 4  |   90000 |    0     | 02-01-2022 | 168000
 5  |    2000 |    0     | 02-01-2022 | 170000

需求说明 #

需要查询所有存在l_id=12的e_id对应的全部记录，但排除其中l_id=12的行（即忽略id为3、6的记录），同时计算滚动余额。

修正后的查询语句 #

SELECT 
    id,
    CASE WHEN `type` = 'Dr' THEN `amt` ELSE 0 END AS Dr,
    CASE WHEN `type` = 'Cr' THEN `amt` ELSE 0 END AS Cr,
    DATE_FORMAT(date, '%d-%m-%Y') AS date,
    SUM(CASE 
            WHEN `type` = 'Dr' THEN -`amt` 
            WHEN `type` = 'Cr' THEN `amt` 
        END) OVER (ORDER BY date, id) AS balance
FROM table_name
WHERE 
    e_id IN (SELECT DISTINCT e_id FROM table_name WHERE l_id = 12)
    AND l_id != 12
ORDER BY date, id;

语句说明 #

1. 子查询SELECT DISTINCT e_id FROM table_name WHERE l_id = 12：先找出所有关联过l_id=12的员工ID（e_id）；
2. 外层筛选条件e_id IN (...) AND l_id != 12：只保留这些员工的记录，同时排除l_id=12的行；
3. DATE_FORMAT(date, '%d-%m-%Y')：将日期格式化为期望的dd-mm-yyyy形式；
4. 窗口函数SUM(...) OVER (ORDER BY date, id)：按日期和ID排序，计算累计滚动余额。

执行结果 #

执行上述修正后的语句，会得到与期望完全一致的输出。

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