Блог

У меня есть эти две таблицы:

 Collect: Date, Rute, Value_Collected...
Records: Date, Rute, Value, Type...
  

Другие столбцы в данном случае не имеют значения. Итак, у меня есть этот рабочий код:

 SELECT
Totals.Date                             AS [Date],
Sum(Totals.Collected)                   AS [Collect],
Sum(Totals.Injected)                    AS [Injections],
Sum(Totals.Spent)                       AS [Expenses],
([Collect]   [Injections]) - [Expenses] AS [Total Day]

FROM (
    SELECT
    Records.Date                        AS [Date],
    0                                   AS [Collected],
    Sum(IIF(Type =  3,Records.Value,0)) AS [Injected],
    Sum(IIF(Type <> 3,Records.Value,0)) AS [Spent]
    FROM Records GROUP BY Records.Date

    UNION ALL

    SELECT
    Collect.Date                 AS [Date],
    Sum(Collect.Value_Collected) AS [Collected],
    0                            AS [Injected],
    0                            AS [Spent]
    FROM Collect GROUP BY Collect.Date
) AS Totals GROUP BY Totals.Date
  

И это дает мне этот результат:

 |Date       |Collect    |Injections  |Expenses   |Total Day
-----------------------------------------------------------
|5/09/2016  |$47.000   |$0           |$0         |$47.000 |
|6/09/2016  |$20.000   |$0           |$0         |$20.000 |
|7/09/2016  |$13.000   |$0           |$0         |$13.000 |
|11/09/2016 |$122.000  |$0           |$0         |$122.000|
|14/09/2016 |$7.000    |$0           |$0         |$7.000  |
|16/09/2016 |$100.000  |$0           |$7.000     |$93.000 |
|30/09/2016 |$0        |$80.000      |$65.000    |$15.000 |
-----------------------------------------------------------
  

И на данный момент это нормально, но формула столбца [Total Day] должна быть рассчитана следующим ([Total Day Of Last Row] [Collect] [Injections]) - [Expenses] образом, а ([Collect] [Injections]) - [Expenses] не давать мне желаемый результат:

 |Date       |Collect      |Injections  |Expenses   |Total Day
-------------------------------------------------------------
|5/09/2016  |$47.000      |$0          |$0         |$47.000 |
|6/09/2016  |$20.000      |$0          |$0         |$67.000 |
|7/09/2016  |$13.000      |$0          |$0         |$80.000 |
|11/09/2016 |$122.000     |$0          |$0         |$202.000|
|14/09/2016 |$7.000       |$0          |$0         |$209.000|
|16/09/2016 |$100.000     |$0          |$7.000     |$302.000|
|30/09/2016 |$0           |$80.000     |$65.000    |$317.000|
-------------------------------------------------------------
  

Но я не знаю, как получить [Total Day] из предыдущей строки для вычисления [Total Day] текущей строки. Если я попытаюсь выполнить подзапрос [Totals] , он выдает ошибку, потому что не может найти таблицу Totals . Я думаю, это потому, что это не «настоящая» таблица, но на самом деле я не знаю, как этого добиться. Я действительно ценю любую помощь.

PS: имена столбцов и таблиц изначально на испанском языке, но переведены для целей, чтобы вы могли понять, что мне нужно выполнить.

По сути, вам нужна кумулятивная сумма по полям по прогрессивным датам, которая может быть обработана с помощью подзапроса correlated sum aggregate. Единственная проблема заключается в том, что MS Access не имеет оконных функций, подобных другим диалектам SQL, для повторного использования запросов производных таблиц. Итак, вам нужно повторить таблицу, производную от union, внутри подзапроса:

 SELECT main.Date, main.Collected AS [Collect], 
       main.Injected As [Injections], main.Spent As [Expenses],
       Ccur((SELECT Sum(sub.Collected)   Sum(sub.Injected) - Sum(sub.Spent) 
             FROM 
               (SELECT Records.Date AS [Date], 0 AS [Collected],
                       Sum(IIF(Type =  3,Records.Value,0)) AS [Injected],
                       Sum(IIF(Type <> 3,Records.Value,0)) AS [Spent]
                FROM Records GROUP BY Records.Date
                UNION ALL
                SELECT Collect.Date AS [Date],
                       Sum(Collect.Value_Collected) AS [Collected],
                       0 AS [Injected],
                       0 AS [Spent]
                FROM Collect GROUP BY Collect.Date
                ) As sub
             WHERE sub.Date <= main.Date)) As [Total Day]

FROM
  (SELECT Records.Date AS [Date], 0 AS [Collected],
          Sum(IIF(Type =  3,Records.Value,0)) AS [Injected],
          Sum(IIF(Type <> 3,Records.Value,0)) AS [Spent]
   FROM Records GROUP BY Records.Date
   UNION ALL
   SELECT Collect.Date AS [Date],
         Sum(Collect.Value_Collected) AS [Collected],
         0 AS [Injected],
         0 AS [Spent]
   FROM Collect GROUP BY Collect.Date
  ) As main
  

К счастью, MS Access поддерживает сохраненный объект запроса, в котором вы сохраняете запрос ОБЪЕДИНЕНИЯ как отдельный запрос и ссылаетесь на него в новом запросе для более короткого сценария:

 SELECT main.Date, main.Collected As [Collect], 
       main.Injected As Injections, main.Spent As Expenses,  
       Ccur((SELECT Sum(sub.Collected)   Sum(sub.Injected) - Sum(sub.Spent) 
             FROM RecordsUnionQ As sub
             WHERE sub.Date <= main.Date)) As [Total Day]
FROM RecordsUnionQ As main
  

Используя SQLite 3, который поддерживает CTE, вы можете использовать WITH() предложение для хранения запроса объединения и избежать его повторения в подзапросе. CTE действуют как временные представления только на время действия инструкции SQL.

 WITH main AS
  (SELECT Records.Date AS [Date], 0 AS [Collected],
          Sum(IIF(Type =  3,Records.Value,0)) AS [Injected],
          Sum(IIF(Type <> 3,Records.Value,0)) AS [Spent]
   FROM Records GROUP BY Records.Date
   UNION ALL
   SELECT Collect.Date AS [Date],
         Sum(Collect.Value_Collected) AS [Collected],
         0 AS [Injected],
         0 AS [Spent]
   FROM Collect GROUP BY Collect.Date)

SELECT main.Date, main.Collected AS [Collect], 
       main.Injected As [Injections], main.Spent As [Expenses],
       Ccur((SELECT Sum(sub.Collected)   Sum(sub.Injected) - Sum(sub.Spent) 
             FROM main As sub
             WHERE sub.Date <= main.Date)) As [Total Day]
FROM main;