Рассчитать баланс с помощью mysql

У меня есть таблица, содержащая следующие данные:

ID      In       Out 
1      100.00    0.00   
2       10.00    0.00   
3        0.00   70.00    
4        5.00    0.00    
5        0.00   60.00   
6       20.00    0.00

Теперь мне нужен запрос, который дает мне следующий результат:

ID      In       Out    Balance
1      100.00    0.00   100.00
2       10.00    0.00   110.00
3        0.00   70.00    40.00
4        5.00    0.00    45.00
5        0.00   60.00   -15.00
6       20.00    0.00     5.00

Можно ли сделать это одним запросом, без использования триггера или хранимых процедур?


mysql sql cumulative-sum balance
person Mannitou    schedule 22.02.2012    source источник

Ответы (3)


arrow_upward
17
arrow_downward

Краткий ответ, да

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

SELECT 
    `table`.`ID`,
    `table`.`In`,
    `table`.`Out`,
    @Balance := @Balance + `table`.`In` - `table`.`Out` AS `Balance`
FROM `table`, (SELECT @Balance := 0) AS variableInit
ORDER BY `table`.`ID` ASC

, (SELECT @Balance := 0) AS variableInit гарантирует, что @Balance инициализируется до 0, прежде чем вы начнете. Затем для каждой строки он устанавливает @Balance равным @Balance + In - Out, а затем выводит вычисленное значение.

Также стоит убедиться, что ПОРЯДОК непротиворечив, иначе Баланс будет варьироваться в зависимости от того, в каком порядке возвращаются строки. Например, если вы хотите заказать его в обратном порядке, вы можете использовать это как подзапрос, поскольку тогда внешний запрос обрабатывает вычисленные значения, таким образом гарантируя, что баланс остается правильным, т.е.

SELECT
    `balanceCalculation`.`ID`,
    `balanceCalculation`.`In`,
    `balanceCalculation`.`Out`,
    `balanceCalculation`.`Balance`
FROM (
    SELECT 
        `table`.`ID`,
        `table`.`In`,
        `table`.`Out`,
        @Balance := @Balance + `table`.`In` - `table`.`Out` AS `Balance`
    FROM `table`, (SELECT @Balance := 0) AS variableInit
    ORDER BY `table`.`ID` ASC
) AS `balanceCalculation`
ORDER BY `balanceCalculation`.`ID` DESC
person Simon at My School Portal    schedule 22.02.2012
comment
как с разбиением на страницы, я думаю, что этот запрос не будет работать, баланс не будет хорошо - person Putra L Zendrato; 27.11.2016
comment
@PutraLZendrato Боюсь, я не понимаю вопроса - person Simon at My School Portal; 29.11.2016
comment
Здравствуйте, Саймон, я имею в виду, как, если строка данных большая, например, у нас есть 100 данных, но они не будут загружаться на одной странице. Итак, разделяем на 2 страницы (работает пагинация). Думаю беговой баланс не сработает. - person Putra L Zendrato; 30.11.2016
comment
Вы можете ограничить внешний запрос, поскольку подзапрос вычисляет производные значения из полного набора данных. Вы можете представить себе balanceCalculation как еще одну таблицу. Ограничение не меняет итоги, а только подмножество данных, которое вы выбираете из него. - person Simon at My School Portal; 04.02.2017
comment
Есть ли способ заставить это работать с нумерацией страниц? - person tinyCoder; 30.03.2018
comment
@tinyCoder, если вы заказываете по идентификатору, вы можете выполнить подзапрос, например, к SELECT DISTINCT table.ID FROM table ORDER BY table.ID ASC LIMIT 0,10, а затем использовать его как ВНУТРЕННЕЕ СОЕДИНЕНИЕ в подзапросе balanceCalculation. В качестве альтернативы менее эффективному методу вы можете просто ОГРАНИЧИТЬ внешний запрос. Но эффективнее было бы ограничить ID, которые суммируются во внутреннем запросе. Но я уже упоминал об этом в комментарии от 2017 года. - person Simon at My School Portal; 04.04.2018
comment
Проблема решена, проверьте это, если вам интересно, спасибо. stackoverflow.com/questions/49836805/ - person tinyCoder; 15.04.2018
comment
Так и будет, но если вы хотите упорядочить на основе итогов по всей таблице, у вас нет другого выбора, кроме как выбрать всю таблицу. Вы можете добавить предложение WHERE в подзапрос, если вас интересует только определенный набор строк, и тогда он будет суммировать только это подмножество данных. - person Simon at My School Portal; 30.07.2018

arrow_upward
4
arrow_downward

Самый простой ответ будет:

SELECT `ID`, 
       `In`, 
       `Out`, 
       @running_bal := @running_bal + (`In` - `Out`)  as `Balance`
FROM   tableName, (SELECT @running_bal := 0) tempName
person John Woo    schedule 22.02.2012

arrow_upward
0
arrow_downward

Достаточно простого LEFT JOIN:

SELECT t.ID, t.In, t.Out, (SUM(t2.In) - SUM(t2.Out)) Balance
FROM mytable t
    LEFT JOIN mytable t2 ON b2.ID <= b.ID
GROUP BY b.ID

Или подзапрос (который, как оказалось, примерно в два раза быстрее)

SELECT t.ID, t.In, t.Out,
    (SELECT SUM(t2.In) - SUM(t2.Out) FROM mytable t2 WHERE t2.ID <= t.ID) Balance
FROM mytable t;
person billynoah    schedule 27.01.2017