mysql - 这段代码是否实现了我想要的？

Question

我正在尝试创建一个查询来评估特定车辆的车主在某个时间点的身份。车辆目击记录包含在 vehicle_sightings 表中。这个查询有点棘手的是 vehicle_vrn 和 vehicle_ownership 表是历史的。所以我需要做的是在目击点获取车辆的 VRN 和车主(基于 vehicle_sightings 表中的 seenDate 字段。

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 的记录将被使用，而其他记录将被使用被忽略。此外，在指定了 end_date 的情况下，这些都无关紧要。这种情况只出现在没有指定 end_date 的情况下，并且有超过 1 个历史条目。

我走在正确的轨道上吗？这个查询有意义吗？它是否也考虑了 vehicle_vrn 历史数据，因为在某些情况下，同一 vrn 有多个条目，但 vrn_start_dates 不同。

Answer 1

你快到了。但是，您的 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)