我有一个表history
,它保存了一个因子和时间戳,当它被改变时,它属于什么区域,像这样:
zone_id | validity_period_start | factor
--------+-----------------------+-------
1 | 1970-01-01 | 50
1 | 2000-03-03 | 70
2 | 2000-02-02 | 90
3 | 2005-08-08 | 20
3 | 2010-10-10 | 30
我有另一个表report
,其中包含日期的区域报告,如下所示:
report_date | zone_id | value
------------+---------+------
2005-09-09 | 1 | 500
2005-09-09 | 2 | 300
2005-09-09 | 3 | 200
2005-09-10 | 1 | 600
2005-09-10 | 2 | 700
2005-09-10 | 3 | 900
现在我想做aSELECT * FROM report WHERE report_date BETWEEN ( "2005-09-09" AND "2005-09-10")
JOIN
为history
。我想要基于history.factor
的返回表中的值,理想情况下是product = value*factor
,在report.report_date
上有效,如下所示:
report_date | zone_id | value | factor | product |
------------+---------+-------+--------+---------+
2005-09-09 | 1 | 500 | 70 | 35000 |
2005-09-09 | 2 | 300 | 90 | 27000 |
2005-09-09 | 3 | 200 | 20 | 4000 |
2005-09-10 | 1 | 600 | 70 | 42000 |
2005-09-10 | 2 | 700 | 90 | 63000 |
2005-09-10 | 3 | 900 | 20 | 18000 |
为方便起见,示例创建了以下代码:
CREATE TABLE rikai_history
(zone_id INT, validity_period_start DATE, factor INT);
INSERT INTO rikai_history VALUES
(1, "1970-01-01", 50),
(1, "2000-03-03", 70),
(2, "2000-02-02", 90),
(3, "2005-08-08", 20),
(3, "2010-10-10", 30);
CREATE TABLE rikai_report
(report_date DATE, zone_id INT, value INT);
INSERT INTO rikai_report VALUES
("2005-09-09", 1, 500),
("2005-09-09", 2, 300),
("2005-09-09", 3, 200),
("2005-09-10", 1, 600),
("2005-09-10", 2, 700),
("2005-09-10", 3, 900);
假设在(zone_id,validity_period_start)和(zone_id,report_date)上都形成了一个PRIMARY KEY…
SELECT x.report_date
, x.zone_id
, x.value
, y.factor
, x.value*y.factor product
FROM
( SELECT r.*
, MAX(validity_period_start) max_start
FROM rikai_report r
JOIN rikai_history h
ON h.zone_id = r.zone_id
AND h.validity_period_start <= r.report_date
GROUP
BY r.report_date,r.zone_id
) x
JOIN rikai_history y
ON y.zone_id = x.zone_id
AND y.validity_period_start = x.max_start ;