咨询MS Access SQL中含UNION的查询如何实现累计求和及日期重复处理

步骤1：为UNION结果生成唯一行号 #

Access没有原生的ROW_NUMBER()函数，但可以通过子查询计数的方式生成类似行号的标识，解决日期重复的问题。

假设你的基础UNION查询结构如下（用UNION ALL保留重复记录，需去重则改用UNION）：

SELECT 交易日期, 收入金额, 支出金额, 交易描述 FROM 表1
UNION ALL
SELECT 交易日期, 收入金额, 支出金额, 交易描述 FROM 表2

给每条记录生成唯一行号，核心是通过子查询统计比当前记录日期更早，或日期相同但其他区分字段更小/相等的记录数：

SELECT 
    t.*,
    (SELECT COUNT(*) 
     FROM (
         SELECT 交易日期, 收入金额, 支出金额, 交易描述 FROM 表1
         UNION ALL
         SELECT 交易日期, 收入金额, 支出金额, 交易描述 FROM 表2
     ) AS sub
     WHERE sub.交易日期 < t.交易日期 
        OR (sub.交易日期 = t.交易日期 
            AND (sub.收入金额 < t.收入金额 
                 OR (sub.收入金额 = t.收入金额 
                     AND sub.交易描述 <= t.交易描述)))) AS 行号
FROM (
    SELECT 交易日期, 收入金额, 支出金额, 交易描述 FROM 表1
    UNION ALL
    SELECT 交易日期, 收入金额, 支出金额, 交易描述 FROM 表2
) AS t
ORDER BY 交易日期, 行号;

步骤2：基于行号实现累计求和 #

在带行号的结果基础上，嵌套子查询计算累计余额（以收入金额-支出金额作为单笔交易变动额）：

SELECT 
    main.交易日期,
    main.收入金额,
    main.支出金额,
    main.交易描述,
    (SELECT SUM(sub.收入金额 - sub.支出金额)
     FROM (
         SELECT 
             t.*,
             (SELECT COUNT(*) 
              FROM (
                  SELECT 交易日期, 收入金额, 支出金额, 交易描述 FROM 表1
                  UNION ALL
                  SELECT 交易日期, 收入金额, 支出金额, 交易描述 FROM 表2
              ) AS sub2
              WHERE sub2.交易日期 < t.交易日期 
                 OR (sub2.交易日期 = t.交易日期 
                     AND (sub2.收入金额 < t.收入金额 
                          OR (sub2.收入金额 = t.收入金额 
                              AND sub2.交易描述 <= t.交易描述)))) AS 行号
         FROM (
             SELECT 交易日期, 收入金额, 支出金额, 交易描述 FROM 表1
             UNION ALL
             SELECT 交易日期, 收入金额, 支出金额, 交易描述 FROM 表2
         ) AS t
     ) AS sub
     WHERE sub.行号 <= main.行号) AS 累计余额
FROM (
    SELECT 
        t.*,
        (SELECT COUNT(*) 
         FROM (
             SELECT 交易日期, 收入金额, 支出金额, 交易描述 FROM 表1
             UNION ALL
             SELECT 交易日期, 收入金额, 支出金额, 交易描述 FROM 表2
         ) AS sub2
         WHERE sub2.交易日期 < t.交易日期 
            OR (sub2.交易日期 = t.交易日期 
                AND (sub2.收入金额 < t.收入金额 
                     OR (sub2.收入金额 = t.收入金额 
                         AND sub2.交易描述 <= t.交易描述)))) AS 行号
    FROM (
        SELECT 交易日期, 收入金额, 支出金额, 交易描述 FROM 表1
        UNION ALL
        SELECT 交易日期, 收入金额, 支出金额, 交易描述 FROM 表2
    ) AS t
) AS main
ORDER BY main.行号;

优化技巧 #

• 若UNION查询逻辑复杂，可先将其保存为查询对象（比如命名为qry_所有交易），后续查询直接引用，避免重复代码：

  -- 先保存qry_所有交易
  SELECT 交易日期, 收入金额, 支出金额, 交易描述 FROM 表1
  UNION ALL
  SELECT 交易日期, 收入金额, 支出金额, 交易描述 FROM 表2;
  
  -- 生成行号的简化写法
  SELECT 
      t.*,
      (SELECT COUNT(*) FROM qry_所有交易 AS sub
       WHERE sub.交易日期 < t.交易日期 
          OR (sub.交易日期 = t.交易日期 
              AND (sub.收入金额 < t.收入金额 
                   OR (sub.收入金额 = t.收入金额 
                       AND sub.交易描述 <= t.交易描述)))) AS 行号
  FROM qry_所有交易 AS t
  ORDER BY 交易日期, 行号;
  

• 若同日期记录无唯一区分字段（金额、描述完全一致），可在UNION时用GUID()生成临时唯一标识辅助行号生成：

  SELECT 交易日期, 收入金额, 支出金额, 交易描述, GUID() AS 临时ID FROM 表1
  UNION ALL
  SELECT 交易日期, 收入金额, 支出金额, 交易描述, GUID() AS 临时ID FROM 表2;
  

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