SELECT CASE WHEN paymentRefund = '退款' THEN CAST(JSON_UNQUOTE(JSON_EXTRACT(TransactionDetails, '$.amount.payer_refund')) AS DECIMAL(18,2)) / 100.00 WHEN paymentRefund = '收款' THEN CAST(JSON_UNQUOTE(JSON_EXTRACT(TransactionDetails, '$.amount.total')) AS DECIMAL(18,2)) / 100.00 ELSE 0.00 END AS Amount FROM t_core_orderaccountlog ;
使用的是 MySQL ,支持 mySQL 5.7+
收款的时候 TransactionDetails 数据格式如下：
'{"mchid":"1743631222","appid":"wx4df35f6abe48cae9","out_trade_no":"c14db90a899811f1bdf4005056812902",
"transaction_id":"4200003128202607277252272143","trade_type":"JSAPI",
"trade_state":"SUCCESS","trade_state_desc":"支付成功","bank_type":"OTHERS","attach":"",
"success_time":"2026-07-27T16:54:44+08:00",
"payer":{"openid":"oq8KX63aPgLLaN1OTWS2eoQI-jb4"},
"amount":{"total":3800,"payer_total":3800,"currency":"CNY","payer_currency":"CNY"}}'
退款的时候 TransactionDetails 数据格式如下：
'{"mchid":"1743631222","out_trade_no":"abe9489839fe11f1bdf4005056812902","transaction_id":"4200003088202604179835385420",
"out_refund_no":"abe9489839fe11f1bdf4005056812902","refund_id":"50302006862026041794805584700","refund_status":"SUCCESS",
"success_time":"2026-04-17T09:44:55+08:00","amount":{"total":1,"refund":1,"payer_total":1,"payer_refund":1},
"user_received_account":"支付用户零钱"}'
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。