从版本控制的表中检索记录

Question

这个sql案例已经困扰了我一段时间，我想在这里问一下其他人的想法。

我有一个拥有车辆的表user，但随着时间的推移，同一辆车可能由多个用户拥有，还有另一个名为effective_date的列，它告诉从哪一天起拥有车辆是有效的。两名驾驶员不拥有同一辆车，但记录是有版本的，这意味着我们可以使用生效日期检查谁在2年前或5年前拥有这辆车。

表有以下列，

id, version, name, vehicle_id, effective_date。对该表的每一次更改都有版本

现在有另一个表称为事故，它告诉车辆发生了什么事故以及何时发生，而不是版本

它有id, description, vehicle_id, acc_date

现在，我正在努力选择所有事故以及谁造成了事故。内部联接在这里不起作用，我所做的是从事故表中选择所有行，并对每一行运行子查询，找到导致事故的用户id和版本。这将非常缓慢，我正在寻找更高效的方式来组织日期或构建查询。现在，它为从事故表中选择的每一行运行一个子查询，因为每一行都有不同的事故日期。如果在一个查询中有一种简单的方法，那么我可以做一些查询。

示例用户表

名称B

	id	版本	vehicle_id	生效日期
1	1			1	2021年10月1日
1	2	A	2021年10月2日
2	1	B	1	2021年10月3日
2	2	2021年10月4日

要获取事故发生时的最新用户，可以使用ROW_NUMBER()按effective_date降序排序。通过此命令，列出的每个事故的第一个用户是负责的用户。

例如：

select *
from (
select *, 
row_number() over(partition by u.vehicle_id
order by effective_date desc) as rn
from user u
join accident a on a.vehicle_id = u.vehicle_id
where u.effective_date <= a.acc_date
) x
where rn = 1

Select user_id, user_version, 
acc_id, vehicle_id, acc_date from(
Select rownumber() over 
(Partition by a.id, a vehicle_id,
b.id) sn ,a.id 
as user_id, a.version 
as user_version,
b.id as acc_id, a.vehicle_id,
acc_date from user a
Inner Join
Accident b on
a.vehicle_id = b.vehicle_id) a 
where sn = 1