Рекурсивный SQL- Как я могу получить эту таблицу с промежуточным итогом?

ID      debit   credit  sum_debit
---------------------------------
1       150     0       150
2       100     0       250
3       0       50      200
4       0       100     100
5       50      0       150

У меня есть эта таблица, моя проблема, как получить sum_debit столбец, который является итогом предыдущего ряда sum_debit с дебетом минус кредит (sum_debit = sum_debit + debit - credit). в каждой новой строке я вношу дебет, но данные кредита равны нулю, или вводя значение кредита, а дебет равен нулю. Как я могу получить sum_debit?

Источник

2 ответа

Решение

Предполагая, что "have" - ​​это ваша таблица данных, это должно быть решение ANSI SQL:

select h.*, sum(i.debit) as debsum, sum(i.credit) as credsum, sum(i.debit) - sum(i.credit) as rolling_sum
from have h inner join have i
on h.id >= i.id
group by h.id, h.debit, h.credit
order by h.id

В общем, решение состоит в том, чтобы соединить строку со всеми строками, предшествующими строке, и извлечь сумму этих строк, а затем сгруппировать все, чтобы вернуться к одной строке в соответствии с тем, что вы ожидаете. Как этот вопрос, например.

Источник

В SQL-Server 2012 вы можете использовать недавно добавленные ROWS или же RANGE пункт:

SELECT 
    ID, debit, credit,
    sum_debit = 
        SUM(debit - credit) 
        OVER (ORDER BY ID
              ROWS BETWEEN UNBOUNDED PRECEDING
                       AND CURRENT ROW
             )
FROM 
    CreditData
ORDER BY
    ID ;

Протестировано в SQL-Fiddle

Мы могли бы просто использовать OVER(ORDER BY ID) там и результат будет тот же. Но тогда будет использоваться значение по умолчанию, которое RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW и есть различия в эффективности (ROWS должно быть предпочтительным с промежуточными итогами.)

Есть замечательная статья rtemp, в которой подробно рассматриваются различные методы вычисления rtemp: лучшие подходы к промежуточным суммам - обновлено для SQL Server 2012

В предыдущих версиях SQL-Server вам придется использовать какой-либо другой метод, например самосоединение, рекурсивный CTE или курсор. Вот курсорное решение, слепо скопированное из блога Аарона, с таблицами и столбцами, адаптированными к вашей проблеме:

DECLARE @cd TABLE
(   [ID] int PRIMARY KEY, 
    [debit] int, 
    [credit] int,
    [sum_debit] int
);

DECLARE
    @ID           INT,
    @debit        INT,
    @credit       INT,
    @RunningTotal INT = 0 ;

DECLARE c CURSOR
    LOCAL STATIC FORWARD_ONLY READ_ONLY
    FOR
    SELECT ID, debit, credit
      FROM CreditData
      ORDER BY ID ;

OPEN c ;

FETCH NEXT FROM c INTO @ID, @debit, @credit ;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @RunningTotal = @RunningTotal + (@debit - @credit) ;

    INSERT @cd (ID, debit, credit, sum_debit )
        SELECT @ID, @debit, @credit, @RunningTotal ;

    FETCH NEXT FROM c INTO @ID, @debit, @credit ;
END

CLOSE c;
DEALLOCATE c;

SELECT ID, debit, credit, sum_debit
    FROM @cd
    ORDER BY ID ;

Проверено в SQL-Fiddle-курсоре

Источник
Другие вопросы по тегам