订单表
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 |
