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