SQL Server Ledger分类账查询结果异常的SQL修正

问题梳理 #

• 运行环境：SQL Server 2014 财务核算库，包含2张核心表
  • Ledger 分类账主表：字段为LID（分类账ID）、LName（分类账名称）、Lgroup（账套分组）、Opbal（期初余额），预置3条测试分类账数据
  • [Transaction] 交易分录表：字段为Txno（凭证号）、Tandate（交易日期）、LID（关联分类账ID）、TXnType（借贷标记）、Amount（发生额）、Narration（分录摘要），预置凭证号1的3条测试分录数据
• 业务要求：指定单个分类账查询时，返回同凭证号下与该分类账对应的对方分录，同时逐笔计算选中分类账的账户余额：
  • 查询Cash（现金）账时，返回对应Salary（工资）、Electricty（电费）的借方分录，同步计算现金逐笔余额
  • 查询Salary、Electricty类费用账时，返回对应Cash（现金）的贷方分录，同步计算对应费用账的逐笔余额
• 原有SQL问题：多表关联未做自身分录过滤，查询Salary账时会返回包含Salary自身在内的全部分录，不符合业务规则。

可直接运行的实现代码 #

代码兼容SQL Server 2014语法，通过参数传入要查询的分类账名称即可返回符合要求的结果：

-- 传入要查询的目标分类账名称，可替换为Cash/Electricty等实际账名
DECLARE @TargetLedger VARCHAR(50) = 'Salary'
DECLARE @TargetLID INT
SELECT @TargetLID = LID FROM Ledger WHERE LName = @TargetLedger

;WITH TargetAcctEntries AS (
    SELECT
        Txno,
        Tandate,
        -- 按记账顺序累计目标账户发生额，借正贷负
        SUM(
            CASE TXnType
                WHEN 'Debit' THEN Amount
                WHEN 'Credit' THEN -Amount
                ELSE 0
            END
        ) OVER (
            ORDER BY Tandate, Txno
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS RunningTotal
    FROM [Transaction]
    WHERE LID = @TargetLID
)
SELECT
    te.Tandate 交易日期,
    te.Txno 凭证号,
    l.LName 对方分类账名称,
    l.Lgroup 对方账分组,
    t.TXnType 对方分录方向,
    t.Amount 对方分录金额,
    t.Narration 摘要,
    -- 期初余额加累计发生额得到当前笔交易后目标账户余额
    (SELECT Opbal FROM Ledger WHERE LID = @TargetLID) + te.RunningTotal 目标账户逐笔余额
FROM TargetAcctEntries te
JOIN [Transaction] t
    ON te.Txno = t.Txno
    -- 核心过滤条件：排除目标账户自身分录，仅保留对方账分录
    AND t.LID <> @TargetLID
JOIN Ledger l
    ON t.LID = l.LID
ORDER BY te.Tandate, te.Txno

关键逻辑说明 #

• 先通过CTE单独提取目标分类账的所有分录，避免多表关联时出现笛卡尔积混入自身数据，从根源解决返回自身分录的问题
• 累计余额计算严格遵循财务记账规则：借方发生额记余额增加，贷方发生额记余额减少
• 关联同凭证分录时增加t.LID <> @TargetLID过滤条件，确保结果仅展示对方分类账的分录信息
• 累计排序字段使用交易日期+凭证号，完全匹配实际记账顺序，余额计算结果准确
• 所有语法均为SQL Server 2014原生支持，无高版本专属函数依赖，可直接上线运行

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