如何計算多個賬戶的流動餘額？

請幫我修復下面的查詢以正確計算執行餘額。

我需要做的是：

• 按 account_no 計算執行餘額（借方-貸方）
• account_no 更改時重置餘額
• 如果 transaction_date 處於新的財務期間且 account_type > 5，則重置餘額

詢問

SELECT 
   acc_account_transactions.id,
   account_no,
   account_type,
   transaction_id,
   DATE_FORMAT(acc_transactions.transaction_date,
           '%d/%m/%Y') transaction_date,
   debit_balance,
   credit_balance,
   IFNULL(acc_finacial_periods.id, - 1),
   (@current_period_id:=IF(IFNULL(acc_finacial_periods.id, - 1) <> @current_period_id,
       IFNULL(acc_finacial_periods.id, - 1),
       @current_period_id)) current_period_id,
   (@running_balance:=IF(@curr_account_no < account_no
           OR (@current_period_id <> IFNULL(acc_finacial_periods.id, - 1)
           AND account_type > 5),
       0,
       @running_balance)) prev_runnng_bal,
   (@curr_account_no:=IF(@curr_account_no < account_no,
       account_no,
       @curr_account_no)) curr_account_no,
   (@running_balance:=@running_balance + (acc_account_transactions.debit_balance - acc_account_transactions.credit_balance)) AS running_balance
FROM
   (SELECT 
       @running_balance:=0,
           @curr_account_no:=0,
           @current_period_id:=- 1
   ) r,
   acc_account_transactions
       INNER JOIN
   acc_transactions ON acc_transactions.id = acc_account_transactions.transaction_id
       INNER JOIN
   acc_accounts ON acc_accounts.id = acc_account_transactions.account_no
       LEFT JOIN
   acc_finacial_periods ON DATE_FORMAT(transaction_date, '%Y/%m/%d') >= DATE_FORMAT(startdate, '%Y/%m/%d')
       AND DATE_FORMAT(transaction_date, '%Y/%m/%d') <= DATE_FORMAT(enddate, '%Y/%m/%d')
ORDER BY account_no , acc_account_transactions.id

現在有什麼問題？

• current_account_no 變數的值錯誤，-它應該與 account_no 列相同-因此，running_balance 結果也不正確

小提琴： 這裡

注意： 數據庫伺服器資訊如下

請不要關閉我的問題，我真的需要幫助：/

編輯

目前錯誤結果如小提琴（最後一個查詢）：

我所期望的，是這樣的：

有什麼不對的，但你可以從這裡拿走。

我替換了您的 FROM 子句，因為您需要一個排序列表來執行該合併，而 mariadb 採用定義，表是未排序的文學並消除了子查詢中的所有排序。所以你必須繞過它

檢查數字，我認為你必須努力。

SELECT  id,account_no,account_type,transaction_id,
    DATE_FORMAT(transaction_date,
            '%d/%m/%Y') transaction_date,
    debit_balance,
    credit_balance,
    IFNULL(afp_id, - 1),
    (@current_period_id:=IF(IFNULL(afp_id, - 1) <> @current_period_id,
        IFNULL(afp_id, - 1),
        @current_period_id)) current_period_id,        
    (@running_balance:=IF(@curr_account_no < account_no
            OR (@current_period_id <> IFNULL(afp_id, - 1)
            AND account_type > 5),
        0,
        @running_balance)) prev_runnng_bal
        ,
    (@curr_account_no:=IF(@curr_account_no < account_no,
        account_no,
        @curr_account_no)) curr_account_no
        ,

    (@running_balance:=@running_balance 
    + (debit_balance - credit_balance)) AS running_balance
FROM

    (SELECT aat.*,at.debit, at.credit, at.transaction_date, aac.account_type
    ,afp.id as afp_id FROM 
    acc_account_transactions aat
        INNER JOIN
      acc_transactions at ON at.id = aat.transaction_id
        INNER JOIN
    acc_accounts aac ON aac.id = aat.account_no
        LEFT JOIN
    acc_finacial_periods afp ON DATE_FORMAT(transaction_date, '%Y/%m/%d') >= DATE_FORMAT(startdate, '%Y/%m/%d')
        AND DATE_FORMAT(transaction_date, '%Y/%m/%d') <= DATE_FORMAT(enddate, '%Y/%m/%d')
        ORDER BY account_no,aat.id LIMIT 18446744073709551615 ) aat1
    ,(SELECT 
        @running_balance:=0,
            @curr_account_no:=0,
            @current_period_id:=- 1
    ) r

db<>在這裡擺弄

擴大創意

SELECT aat.*,at.debit, at.credit, at.transaction_date, aac.account_type
   ,afp.id as afp_id FROM 
   acc_account_transactions aat
       INNER JOIN
     acc_transactions at ON at.id = aat.transaction_id
       INNER JOIN
   acc_accounts aac ON aac.id = aat.account_no
       LEFT JOIN
   acc_finacial_periods afp ON DATE_FORMAT(transaction_date, '%Y/%m/%d') >= DATE_FORMAT(startdate, '%Y/%m/%d')
       AND DATE_FORMAT(transaction_date, '%Y/%m/%d') <= DATE_FORMAT(enddate, '%Y/%m/%d')
       ORDER BY account_no,aat.id LIMIT 18446744073709551615

為計算奠定基礎。

這是一個新的子查詢，它對**給定的數據進行排序，**以便您獲得想要的結果。

在LIMIT 18446744073709551615 那裡，因為當 mariadb 處理上面的查詢時，它會刪除需要的訂單。

SELECT aat.*,at.debit, at.credit, at.transaction_date, aac.account_type
   ,afp.id as afp_id   

是計算所需的列，如果您需要更多列，則首先必須在此處包含它們。請注意 mysql/mariadb 不喜歡相同的列名，所以我必須afp.id as afp_id給 secnd id fiekld 一個別名。

引用自：https://dba.stackexchange.com/questions/269084