Mysql
如何計算多個賬戶的流動餘額?
請幫我修復下面的查詢以正確計算執行餘額。
我需要做的是:
- 按 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 一個別名。