时间戳

首页 > 解决方案 > 使用mysql计算在同一列中贷记和借记的每种货币的余额？

mysql - 使用mysql计算在同一列中贷记和借记的每种货币的余额？

问题描述

使用以下 MySQL 表，其中包含关联金额和货币的借方或贷方“操作”，如何选择所有CLIENT_IDs非零"balance"的currency？我试过了，但有些东西不能正常工作。

CLIENT_ID    ACTION_TYPE    ACTION_AMOUNT    Currency
1            debit          1000                USD
1            credit         100                 USD
1            credit         500                 DR
2            debit          1000                EURO
2            credit         1200                DR
3            debit          1000                EURO
3            credit         1000                USD
4            debit          1000                USD

我的 MySQL 查询不起作用：

SELECT client_id,
       SUM(if(action_type='credit',action_amount,0)) as credit,
       SUM(if(action_type='debit',action_amount,0 )) as debit,
       SUM(if(currency='USD' ,action_type='credit'- action_type='debit' )) as USD_balance,
       SUM(if(currency='EURO',action_type='credit'- action_type='debit' )) as EURO_balance,
       SUM(if(currency='DR'  ,action_type='credit'- action_type='debit' )) as DR_balance
  FROM my_table
 GROUP BY client_id,currency

我期待的结果是这样的：

CLIENT_ID    USD_Balance    EURO_Balance    DR_Balance
1              -900             0              500
2                0            -1000            1200
3              1000           -1000             0
4             -1000              0              0

我不知道还能尝试什么。任何帮助都会很棒。

标签： mysqlsql

解决方案

您可以Group By client_id包括Conditional Aggregation如下

SELECT client_id,
       SUM(case when action_type = 'credit' then action_amount else 0 end) as credit,
       SUM(case when action_type = 'debit'  then action_amount else 0 end) as debit,
       SUM(case
             when currency = 'USD' and action_type = 'credit' then
              action_amount else 0
           end) - SUM(case
                        when currency = 'USD' and action_type = 'debit' then
                         action_amount
                        else 0
                      end) as USD_balance,
       SUM(case
             when currency = 'EURO' and action_type = 'credit' then
              action_amount else 0
           end) - SUM(case
                        when currency = 'EURO' and action_type = 'debit' then
                         action_amount
                        else 0
                      end) as EUR_balance,
       SUM(case
             when currency = 'DR' and action_type = 'credit' then
              action_amount
             else 0
           end) - SUM(case
                        when currency = 'DR' and action_type = 'debit' then
                         action_amount
                        else 0
                      end) as DR_balance
  FROM my_table
 GROUP BY client_id;

Demo

推荐阅读