订单表 select * from sales order by date; date currency val 2022-01-01 rmb 10.00 2022-01-01 hk 100.00 2022-02-02 rmb 100.00 2022-03-01 hk 10.00 2022-04-02 hk 100.00 2022-04-02 rmb 100.00 汇率表 select * from rate order by date; date currency val 2022-01-01 rmb 6.30 2022-01-01 hk 7.20 2022-02-03 rmb 6.50 2022-02-03 hk 7.80 求统计记录usd 每日的销售额 select s.date, sum(s.val *(select i.val from rate i where s.date >= i.date and i.currency=s.currency order by i.date desc limit 1)) as usd from sales s group by s.date order by s.date asc; date usd 2022-01-01 783.0000 2022-02-02 630.0000 2022-03-01 78.0000 2022-04-02 1430.0000
