请教大神在同一TABLE进行金额栏位乘除后COUNT 并区分能达成一行显示吗

小弟在统计临柜汇出汇款笔数、金额时卡住了.....

想询问版上的大大,下图为最终显示结果
小弟该如何下指令会比较好....http://img2.58codes.com/2024/emoticon02.gif
http://img2.58codes.com/2024/20137200ygJ1u7vkh7.png

下方是小弟目前算出汇出的币别并折成美金

SELECT A.REMIT_CCY,COUNT(0) ,
CASE WHEN REMIT_CCY ='AUD' THEN SUM(ROUND(REMIT_AMT * B.EXRATE_EVALUATE,2))
WHEN REMIT_CCY ='CAD' THEN SUM(ROUND(REMIT_AMT / B.EXRATE_EVALUATE,2))
WHEN REMIT_CCY ='CHF' THEN SUM(ROUND(REMIT_AMT / B.EXRATE_EVALUATE,2))
WHEN REMIT_CCY ='CNY' THEN SUM(ROUND(REMIT_AMT / B.EXRATE_EVALUATE,2))
WHEN REMIT_CCY ='EUR' THEN SUM(ROUND(REMIT_AMT * B.EXRATE_EVALUATE,2))
WHEN REMIT_CCY ='GBP' THEN SUM(ROUND(REMIT_AMT * B.EXRATE_EVALUATE,2))
WHEN REMIT_CCY ='HKD' THEN SUM(ROUND(REMIT_AMT / B.EXRATE_EVALUATE,2))
WHEN REMIT_CCY ='JPY' THEN SUM(ROUND(REMIT_AMT / B.EXRATE_EVALUATE,2))
WHEN REMIT_CCY ='NZD' THEN SUM(ROUND(REMIT_AMT * B.EXRATE_EVALUATE,2))
WHEN REMIT_CCY ='SGD' THEN SUM(ROUND(REMIT_AMT / B.EXRATE_EVALUATE,2))
WHEN REMIT_CCY ='ZAR' THEN SUM(ROUND(REMIT_AMT / B.EXRATE_EVALUATE,2))
WHEN REMIT_CCY ='USD' THEN SUM(ROUND(REMIT_AMT * B.EXRATE_EVALUATE,2)) END AS 折美金额
FROM FRORTM A
LEFT JOIN CMXRVM_CBUS B ON A.REMIT_CCY=B.CCY AND B.VALID_DATE ='20240200'
WHERE TXNT_DATE>='20240201' and TXNT_DATE<='20240231' GROUP BY REMIT_CCY ORDER BY REMIT_CCY

http://img2.58codes.com/2024/20137200U4AduBwjxR.png

下图是区分法金 / 个金 判别方式

select CLIENT_CODE,sum(CNT) from ( select case when SUBSTRING(REMITTER_ID,9,1)=' ' then '法金' else '个金' end as CLIENT_CODE, count(*) as CNT from FRORTM where TXNT_DATE>='20240201' and TXNT_DATE<='20240231' group by SUBSTRING(REMITTER_ID,9,1) ) as d group by CLIENT_CODE ORDER BY 1

http://img2.58codes.com/2024/20137200RJpkqR80cu.png


关于作者: 网站小编

码农网专注IT技术教程资源分享平台,学习资源下载网站,58码农网包含计算机技术、网站程序源码下载、编程技术论坛、互联网资源下载等产品服务,提供原创、优质、完整内容的专业码农交流分享平台。

热门文章