附：自动分配金额SQL Server实现方法
3.2 完整代码(复制到SQL Server直接运行可出结果)
代码▼
WITH 发票数据 AS (
SELECT 1 AS 序号, 'CG250323010' AS 单据编号, '2025-03-23' AS 日期,
'' AS 摘要, 756 AS 金额, 300 AS 余额
UNION ALL
SELECT 2, 'CG250323011', '2025-03-23', '', 207, 100
),
基础数据 AS (
SELECT *, 360 AS 待分配金额 FROM 发票数据
),
带累计余额 AS (
SELECT
当前行.*,
(SELECT SUM(余额)
FROM 基础数据
WHERE 序号 <= 当前行.序号) AS 累计余额
FROM 基础数据 当前行
),
带前累计余额 AS (
SELECT
当前行.*,
ISNULL((SELECT SUM(余额)
FROM 基础数据
WHERE 序号 < 当前行.序号), 0) AS 前累计余额
FROM 带累计余额 当前行
)
SELECT
序号, 单据编号, 日期, 摘要, 金额, 余额, 待分配金额,
CASE
WHEN 待分配金额 >= 累计余额 THEN 余额
WHEN 待分配金额 > 前累计余额 THEN 待分配金额 - 前累计余额
ELSE 0
END AS 结算金额
FROM 带前累计余额
ORDER BY 序号;
