下面的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
我不知道还能尝试什么。任何帮助都很好。
最佳答案
您可以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