Access 2010客户分类账报表性能优化及期初余额SQL查询需求

Access 2010可用SQL查询 #

直接用UNION ALL合并期初余额行与本期记录，同时解决“Opening Balance”显示问题，彻底避免报表重复计算：

SELECT 
    0 AS id,
    NULL AS date,
    [ac_id],
    "Opening Balance" AS narration,
    0 AS dr,
    0 AS cr,
    (SELECT SUM(dr - cr) FROM final_ledger WHERE ac_id = FL.ac_id AND date < [DateFrom]) AS balance,
    1 AS sort_order
FROM final_ledger FL
WHERE ac_id = [TargetAcID] -- 替换为目标客户ID参数
GROUP BY ac_id

UNION ALL

SELECT 
    id,
    date,
    ac_id,
    narration,
    dr,
    cr,
    balance,
    2 AS sort_order
FROM final_ledger
WHERE ac_id = [TargetAcID] -- 替换为目标客户ID参数
AND date BETWEEN [DateFrom] AND [DateTo]

ORDER BY sort_order, date, id;

逻辑说明 #

• 第一个查询：按客户ID分组，计算指定日期前的dr-cr总和作为期初余额，narration直接设为“Opening Balance”，sort_order=1确保该行排在最前面
• 第二个查询：筛选指定日期范围内的客户交易记录，sort_order=2让其排在期初行之后
• 最终按sort_order、date、id排序，保证记录顺序正确

性能优化建议 #

给final_ledger表的ac_id和date字段建立联合索引，可大幅提升大数据量下的查询效率，彻底解决原报表每条记录重复执行DSUM导致的卡顿问题。

若Access性能仍不足，建议切换至MySQL #

MySQL下的SQL结构类似，仅参数传递方式略有不同，同时可借助数据库引擎的性能优势处理更大数据量：

SELECT 
    0 AS id,
    NULL AS `date`,
    ac_id,
    'Opening Balance' AS narration,
    0 AS dr,
    0 AS cr,
    (SELECT SUM(dr - cr) FROM final_ledger WHERE ac_id = FL.ac_id AND `date` < ?) AS balance,
    1 AS sort_order
FROM final_ledger FL
WHERE ac_id = ?
GROUP BY ac_id

UNION ALL

SELECT 
    id,
    `date`,
    ac_id,
    narration,
    dr,
    cr,
    balance,
    2 AS sort_order
FROM final_ledger
WHERE ac_id = ?
AND `date` BETWEEN ? AND ?

ORDER BY sort_order, `date`, id;

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