mysql - 连接表时使用最近的日期来查找缺失的日期

Question

我有 2 张 table :

1.tran_test : (id, amount, currency(Currency_Name), date_1)
2.cur_test: (id, currency, date_2, price(amount In USD))

考虑到 cur.test 表中缺少某些日期，因此您需要使用最后一个可用日期来计算结果，如何获取每天的销售额总额？

我编写了一个计算总和的查询，但它排除了汇率缺失的日期。

如何计算缺失天数的金额？

SELECT date_1, SUM(amount*c.price) AS sum_by_day
 FROM tran_test AS t
 INNER JOIN cur_test AS c
 ON t.currency = c.currency AND date_1 = date_2
 GROUP BY date_1;

我不需要只生成日期。我需要使用表中可用的数据来计算金额。如果缺少某些日期，我需要采用货币汇率可用的最后一个可用日期。例如 - 表 tran.test 有 9 月 28 日的交易，但未提供这一天的汇率，因此我需要采用最后的可用汇率

相似数据示例

INSERT INTO tran_test (amount, currency, date_1) VALUES 
(1000,'RUB','2018-09-01'),
(15,'USD','2018-09-01'),
(10,'EUR','2018-09-01'),

INSERT INTO cur_test (name,date_2, price) VALUES
('RUB','2018-01-09',0.02),
('USD','2018-01-09',1),

输出应该是这样的日期 - 该日期的总金额2018-02-21 - 2128

完整代码在这里 - https://www.db-fiddle.com/f/v4SHW9XgtgyehbhanQFRx5/2实际数据稍微复杂一些，包括 UNIX 格式的日期

Answer 1

编写一个子查询，为交易表中的每个日期查找货币表中的最新日期。

SELECT t.date_1, MAX(c.date_2) AS latest_date
FROM tran_test AS t
JOIN cur_test AS c ON t.date_1 >= c.date_2
GROUP BY t.date1

然后您可以将其与查询的其余部分结合起来。

SELECT t.date_1, SUM(t.amount * c.price) AS sum_by_day
FROM tran_test AS t
JOIN (
    SELECT t.date_1, MAX(c.date_2) AS latest_date
    FROM tran_test AS t
    JOIN cur_test AS c ON t.date_1 >= c.date_2
    GROUP BY t.date1) AS g ON g.date_1 = t.date_1
JOIN cur_test AS c ON c.date_2 = g.latest_date
GROUP BY t.date_1