MySQL查询未返回预期结果：车辆日志状态筛选问题

数据库表结构 #

车辆表（asset_vehicle） #

CREATE TABLE `asset_vehicle` (
  `id` varchar(50) NOT NULL,
  `asset_code` varchar(50) NOT NULL,
  `engine_capacity` varchar(20) NOT NULL,
  `engine_no` varchar(50) NOT NULL,
  `make` varchar(80) NOT NULL,
  `model` varchar(50) NOT NULL,
  `no_of_doors` varchar(3) DEFAULT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

车辆日志表（asset_vehicle_logs） #

CREATE TABLE `asset_vehicle_logs` (
  `id_pk` varchar(50) NOT NULL,
  `description` varchar(2000) NOT NULL,
  `id` varchar(40) NOT NULL,
  `date_log` date DEFAULT NULL,
  `time_log` time DEFAULT NULL,
  PRIMARY KEY (`id_pk`),
  KEY `FK7up0y9054d64e3mvue3r4pe9e` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

其中id是车辆表的主键，同时作为车辆日志表的外键。

业务场景 #

插入车辆时，日志表自动生成vehicle added记录；车辆状态变更时，日志表同步新增对应状态记录。

当前问题 #

需要筛选2023-12-01至2023-12-31范围内车辆的最新状态，要求同一车辆不会同时出现在不同状态的报告中。但当前编写的两个查询（分别查询"Vehicle verified"和"Vehicle rejected"状态）会返回同一车辆，不符合预期。

当前查询语句 #

查询1（获取已验证车辆）

select * from asset_vehicle_logs l join asset_vehicle v on l.id = v.id where l.description = 'Vehicle verified' and  l.date_log between '2023-12-01' and '2023-12-31' group by l.id  order by l.date_log desc,l.time_log desc;

查询2（获取已拒绝车辆）

select * from asset_vehicle_logs l join asset_vehicle v on l.id = v.id where l.description = 'Vehicle rejected' and  l.date_log between '2023-12-01' and '2023-12-31' group by l.id  order by l.date_log desc,l.time_log desc;

补充信息 #

• 车辆表示例数据：包含车辆ID、资产编码、引擎排量、引擎编号、品牌、型号、车门数量等字段的样本数据
• 日志表示例数据（按车辆ID筛选）：
  • 示例1：某车辆在指定时间范围内有多条日志记录，包含不同状态及对应的日期、时间
  • 示例2：某车辆在指定时间范围内的最新状态为"Vehicle verified"
  • 示例3：某车辆在指定时间范围内的最新状态为"Vehicle rejected"

解决方案 #

核心思路是先锁定每个车辆在指定时间内的最新日志记录，再基于这条记录的状态筛选，确保同一车辆只出现在对应最新状态的报告中。

步骤1：获取每个车辆的最新日志时间 #

SELECT 
    id,
    MAX(CONCAT(date_log, ' ', time_log)) AS latest_datetime
FROM asset_vehicle_logs
WHERE date_log BETWEEN '2023-12-01' AND '2023-12-31'
GROUP BY id;

步骤2：关联获取完整最新状态及车辆信息 #

基于上述子查询，关联日志表和车辆表，得到每个车辆的最新状态记录：

SELECT 
    l.*, v.*
FROM asset_vehicle_logs l
JOIN asset_vehicle v ON l.id = v.id
JOIN (
    SELECT 
        id,
        MAX(CONCAT(date_log, ' ', time_log)) AS latest_datetime
    FROM asset_vehicle_logs
    WHERE date_log BETWEEN '2023-12-01' AND '2023-12-31'
    GROUP BY id
) latest ON l.id = latest.id AND CONCAT(l.date_log, ' ', l.time_log) = latest.latest_datetime;

步骤3：按状态生成目标报告 #

已验证车辆报告

SELECT 
    l.*, v.*
FROM asset_vehicle_logs l
JOIN asset_vehicle v ON l.id = v.id
JOIN (
    SELECT 
        id,
        MAX(CONCAT(date_log, ' ', time_log)) AS latest_datetime
    FROM asset_vehicle_logs
    WHERE date_log BETWEEN '2023-12-01' AND '2023-12-31'
    GROUP BY id
) latest ON l.id = latest.id AND CONCAT(l.date_log, ' ', l.time_log) = latest.latest_datetime
WHERE l.description = 'Vehicle verified';

已拒绝车辆报告

SELECT 
    l.*, v.*
FROM asset_vehicle_logs l
JOIN asset_vehicle v ON l.id = v.id
JOIN (
    SELECT 
        id,
        MAX(CONCAT(date_log, ' ', time_log)) AS latest_datetime
    FROM asset_vehicle_logs
    WHERE date_log BETWEEN '2023-12-01' AND '2023-12-31'
    GROUP BY id
) latest ON l.id = latest.id AND CONCAT(l.date_log, ' ', l.time_log) = latest.latest_datetime
WHERE l.description = 'Vehicle rejected';

方案说明 #

• 原查询直接按id分组，但未保证取到该车辆的最新状态记录，导致同一车辆可能在不同状态报告中出现
• 新方案先通过子查询锁定每个车辆的最新日志时间，再关联获取对应记录，确保每个车辆仅返回一条最新状态的记录

内容的提问来源于stack exchange，提问作者Nipun Vidarshana