mysql - 这段代码能实现我想要的吗？

我试图创建一个查询，该查询评估特定车辆的所有者在某个时间点是谁。车辆视线包含在vehicle_sightings表中。这个查询稍微有点棘手的是，vehicle_vrn和vehicle_ownership表是历史的。因此，我需要做的是在瞄准点（基于车辆瞄准表中的seenDate字段）获取车辆的vrn和车主。

SELECT
    sighting_id
FROM
    vehicle_sightings
        INNER JOIN
    vehicle_vrn ON vehicle_sightings.plate = vehicle_vrn.vrnno
        INNER JOIN
    vehicle_ownership ON vehicle_vrn.fk_sysno = vehicle_ownership.fk_sysno
WHERE
    vehicle_sightings.seenDate >= vehicle_ownership.ownership_start_date
        AND (vehicle_sightings.seenDate <= vehicle_ownership.ownership_end_date
        OR vehicle_ownership.ownership_end_date IS NULL
        OR vehicle_ownership.ownership_end_date = '0001-01-01 00:00:00')
GROUP BY sighting_id
    HAVING seenDate >= MAX(ownership_start_date);

我已经尝试了上述查询的许多变体，但除了上面粘贴的查询之外，它们似乎都没有获得所需的结果。然而，我担心的是，它并没有像我希望的那样工作，因为我对GROUP BY语句没有太多经验。
因此，我想要的是，在下面的屏幕截图中，ownership_start_date最接近seenDate的记录被使用，其他记录被忽略。此外，如果已经指定了结束日期，则不必担心这些问题。此方案仅在未指定结束日期且有1个以上历史条目的情况下出现。
我走对了吗？这个问题有意义吗？它是否也考虑了vehicle_vrn历史数据，因为同样的vrn也可能有多个条目，但有不同的vrn_start_dates。

最佳答案

你就快到了。但是，您的HAVING子句不会有任何效果（每个组中最新的ownership_start_date必须在seenDate之前，因为您已经明确要求WHERE子句中的每个组成记录的）。
您需要的是group-wise maximum，它可以通过将分组的结果连接回基础表来获得。例如：

SELECT * FROM vehicle_ownership JOIN (
  SELECT
      vehicle_sightings.*,
      vehicle_ownership.fk_sysno,
      MAX(vehicle_ownership.ownership_start_date) AS ownership_start_date
  FROM
      vehicle_sightings
          INNER JOIN
      vehicle_vrn ON vehicle_sightings.plate = vehicle_vrn.vrnno
          INNER JOIN
      vehicle_ownership ON vehicle_vrn.fk_sysno = vehicle_ownership.fk_sysno
  WHERE
      vehicle_sightings.seenDate >= vehicle_ownership.ownership_start_date
          AND (vehicle_sightings.seenDate <= vehicle_ownership.ownership_end_date
          OR vehicle_ownership.ownership_end_date IS NULL
          OR vehicle_ownership.ownership_end_date = '0001-01-01 00:00:00')
  GROUP BY sighting_id
) t USING (fk_sysno, ownership_start_date)