我正在尝试加入最近的日期,有 2 张桌子(从人到受伤(,但前提是过去一个月内有记录。
示例表/输出:
PEOPLE
Name Date
John Smith 01/01/2016
Jerry Doe 01/14/2016
Jane Ellis 02/21/2016
Adam Patel 03/21/2016
INJURIES
Injury Date
Broken Hand 12/30/2015
Broken Wrist 12/31/2015
Head Pain 01/13/2016
Broken Hand 02/02/2016
OUTPUT1 (Able to achieve, but not desired)
Name Injury
John Smith Broken Hand
John Smith Broken Wrist
Jerry Doe Broken Hand
Jerry Doe Broken Wrist
Jerry Doe Head Pain
Jane Ellis Broken Hand
Adam Patel {null}
OUTPUT2 (Desired Output)
Name Injury
John Smith Broken Wrist
Jerry Doe Head Pain
Jane Ellis Broken Hand
Adam Patel {null}
有没有办法在流程的任何步骤中不获取 output1 即可进行连接?我正在处理大量记录,并希望以尽可能少的计算能力进行此连接。
即使这是一个糟糕的设计,此查询也将返回所需的结果:
select
p.name,
(select injury
from injuries x
where x.date between p.date - 1 month and p.date
order by x.date desc
fetch first 1 row only)
from
people p;
出于性能原因,您需要提供一些其他详细信息,但您可能希望在INJURIES.DATE
上有一个索引(最好按降序排列(。
我建议您寻找正确的键来连接表,因为单独使用日期似乎是获取错误数据的好方法。