MSSQL 数据库有个资产账户的流水表，日期不连续。
| Name | Date | Debit | Credit |
| A | 2021-01-01 | 10 | 0 |
| A | 2021-01-01 | 9 | 0 |
| A | 2021-02-01 | 11 | 0 |
| A | 2021-03-01 | 0 | 50 |
| A | 2021-04-01 | 30 | 0 |
| B | 2021-01-01 | 10 | 0 |
| B | 2022-02-01 | 0 | 12 |
| B | 2022-03-01 | 0 | 50 |
| B | 2024-04-01 | 3 | 0 |
现在要统计从期初 2021 年 1 月到期末 2024 年 4 月每个账户每个月的余额，缺失的月份要补齐。
| Name | y | m | Blance |
| A | 2021 | 1 | -19 |
| A | 2021 | 2 | -30 |
| A | 2021 | 3 | 20 |
| A | 2021 | 4 | -10 |
| A | 2021 | 5 | -10 |
| … | … | … | … |
| A | 2024 | 3 | -10 |
| A | 2024 | 4 | -10 |
| B | 2021 | 1 | -10 |
| B | 2021 | 2 | -10 |
| … | … | … | … |
| B | 2022 | 1 | -10 |
| B | 2022 | 2 | 2 |
| B | 2022 | 3 | 52 |
| B | 2022 | 4 | 52 |
| … | … | … | … |
| B | 2024 | 3 | 52 |
| B | 2024 | 4 | 49 |
SQL 解法：
WITH Accounts AS (SELECT DISTINCT Name FROM trans
),
Months AS (SELECT DATEADD(MONTH, n, '2021-01-01') AS MonthStartFROM (SELECT TOP (DATEDIFF(MONTH, '2021-01-01', '2024-04-01') + 1) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS nFROM master.dbo.spt_values) AS Numbers
),
AccountMonths AS (SELECT a.Name, m.MonthStartFROM Accounts aCROSS JOIN Months m
),
Changes as (SELECT Name, DATEADD(MONTH, DATEDIFF(MONTH, 0, Date), 0) as ym,sum(Credit - Debit) as change FROM trans group by Name, DATEADD(MONTH, DATEDIFF(MONTH, 0, Date), 0)
),
fullChanges as (SELECT A.Name,A.MonthStart,c.change FROM AccountMonths a left join Changes c on a.Name=c.Name and a.MonthStart=c.ym
)
SELECT Name,YEAR(MonthStart) AS Y,MONTH(MonthStart) AS M,SUM(change) OVER (PARTITION BY Name ORDER BY MonthStart) AS balanceFROM fullChangesSQL没有方便的方法生成月份序列，要用嵌套查询+窗口函数，代码非常复杂。
SPL提供了生成日期序列的函数，包括连续月份。
| A | |
| 1 | =mssql.query("select Name, DATEADD(MONTH, DATEDIFF(MONTH, 0, Date), 0)as ym,sum(Credit - Debit) as change from trans group by Name, DATEADD(MONTH, DATEDIFF(MONTH, 0, Date), 0)”) |
| 2 | =periods@m(date("2021-01-01"),date("2024-04-01"),1) |
| 3 | =xjoin(A1.id(Name):Name; A2:ym) |
| 4 | =A3.join(Name:ym, A1:Name:ym,change) |
| 5 | =A4.new(Name,year(ym):y,month(ym):m,change+if(Name==Name[-1] , Balance[-1]):Balance) |
A1：查询数据库，按账户、每月第1天的日期分组，统计每月金额变化。
A2：生成每月第一天组成的连续序列。perionds生成日期序列，@m表示间隔单位为月份。
A3：将账户和日期序列进行叉乘。
A4：将叉乘结果和A1左关联。
A5：当前账号与上一条记录相比不变时，当月余额=当月金额变化+上个月的余额；账号变化时，当月余额重置为当月金额变化。
SPL已开源免费，欢迎前往乾学院了解更多！
源码地址
免费下载
