如何使用子查询的别名获取运行总计

Question

我有 3 个表的UNION用于计算一些余额，我需要获取该余额的运行SUM，但我不能使用 PARTITION OVER ，因为我必须使用可以在 Access 中工作的 sql 查询来执行此操作。我的问题是我不能在别名子查询上使用JOIN，它不起作用。

如何在JOIN中使用别名来获取运行总计？

或任何其他获取SUM的方法，该PARTITION OVER ，因为它在 Access 中不存在。这是我到目前为止的代码：

SELECT korisnik_id, imePrezime, datum, Dug, Pot, (Dug - Pot) AS Balance
FROM (
    SELECT korisnik_id, k.imePrezime, r.datum, SUM(IIF(u.jedinstven = 1, r.cena, k.kvadratura * r.cena)) AS Dug, '0' AS Pot
    FROM Racun r
    INNER JOIN Usluge u ON r.usluga_id = u.ID
    INNER JOIN Korisnik k ON r.korisnik_id = k.ID
    WHERE korisnik_id = 1
        AND r.zgrada_id = 1
        AND r.mesec = 1
        AND r.godina = 2017
    GROUP BY korisnik_id, k.imePrezime, r.datum
    UNION ALL
    SELECT korisnik_id, k.imePrezime, rp.datum, SUM(IIF(u.jedinstven = 1, rp.cena, k.kvadratura * rp.cena)) AS Dug, '0' AS Pot
    FROM RacunP rp
    INNER JOIN Usluge u ON rp.usluga_id = u.ID
    INNER JOIN Korisnik k ON rp.korisnik_id = k.ID
    WHERE korisnik_id = 1
        AND rp.zgrada_id = 1
        AND rp.mesec = 1
        AND rp.godina = 2017
    GROUP BY korisnik_id, k.imePrezime, rp.datum
    UNION ALL
    SELECT uu.korisnik_id, k.imePrezime, uu.datum, '0' AS Dug, SUM(uu.iznos) AS Pot
    FROM UnosUplata uu
    INNER JOIN Korisnik k ON uu.korisnik_id = k.ID
    WHERE korisnik_id = 1
    GROUP BY uu.korisnik_id, k.imePrezime, uu.datum
    ) AS a
ORDER BY korisnik_id

Answer 1

您可以为 3 个表的UNION保存一个查询(我们将其命名为 Query1 (，然后创建另一个查询，该查询返回第一个查询中的每一行并计算其之前行的总和(可选地检查它们是否在同一组中(。

它应该是这样的：

SELECT *, (
    SELECT SUM(Value) FROM Query1 AS b 
    WHERE b.GroupNumber=a.GroupNumber
    AND b.Position<=a.Position
) AS RunningSum
FROM Query1 AS a

但是，在报告中执行此操作更有效。