MySQL 5.6中用LEFT JOIN查询车辆最新租赁记录及状态

表结构 #

使用MySQL 5.6，拥有两张关联表：

Cars表 #

CREATE TABLE Cars (
    id INT NOT NULL AUTO_INCREMENT,
    plate VARCHAR(16) NOT NULL,
    flag TINYINT,
    PRIMARY KEY(id)
);

Rents表 #

CREATE TABLE Rents (
    id INT NOT NULL AUTO_INCREMENT,
    out_date DATE NOT NULL,
    in_date DATE,
    car_id INT,
    FOREIGN KEY (car_id) REFERENCES Cars(id),
    PRIMARY KEY(id)
);

每辆车可对应0至多条租赁记录。

查询需求 #

查询Cars表中所有flag=1的车辆，同时获取它们的当前状态：

• 不可用：仅填写out_date（未归还）
• 可用：out_date和in_date均填写（已归还），无租赁记录的车辆也视为可用
  结果集需包含out_date和in_date的值。

尝试及问题 #

尝试了以下查询语句：

SELECT 
    *,
    IF(Rents.in_date IS NOT NULL AND Rents.out_date IS NOT NULL, 1, IF(Rents.id IS NULL, 1, 0)) AS status
FROM Cars 
LEFT JOIN Rents ON Cars.id = Rent.Car_id WHERE Cars.Flag = 1;

但该查询会返回所有匹配flag的车辆的所有租赁记录，结果如下：

id | plate    | flag | id | out_date  | in_date      | car_id | status
---------------------------------------------------------------------
'1', 'FA787MX', '1',  '1', '2022-07-14', '2022-07-15', '1',     '1'
'1', 'FA787MX', '1',  '2', '2022-07-16', NULL,         '1',     '0'
'3', 'AB124DF', '1',  '4', '2022-07-13', '2022-07-14', '3',     '1'
'4', 'CC666VC', '1',  NULL, NULL,        NULL,         NULL,    '1'
'5', 'GG435ED', '1',  '5', '2022-07-16', NULL,         '5',     '0'

需要的是每辆车仅保留最新out_date的租赁记录（无论租赁记录id高低），期望结果如下：

'1', 'FA787MX', '1',  '2', '2022-07-16', NULL,         '1',     '0'
'3', 'AB124DF', '1',  '4', '2022-07-13', '2022-07-14', '3',     '1'
'4', 'CC666VC', '1',  NULL, NULL,        NULL,         NULL,    '1'
'5', 'GG435ED', '1',  '5', '2022-07-16', NULL,         '5',     '0'

补充说明：租赁记录的id与租赁历史顺序无必然关联，不能通过in_date是否为NULL判断正确记录，必须通过out_date比较确定最新记录。

测试数据 #

INSERT INTO `Cars` (`id`, `plate`, `flag`) VALUES (1, 'FA787MX', 1); 
INSERT INTO `Cars` (`id`, `plate`, `flag`) VALUES (2, 'EX431YY', 0); 
INSERT INTO `Cars` (`id`, `plate`, `flag`) VALUES (3, 'AB124DF', 1); 
INSERT INTO `Cars` (`id`, `plate`, `flag`) VALUES (4, 'CC666VC', 1); 
INSERT INTO `Cars` (`id`, `plate`, `flag`) VALUES (5, 'GG435ED', 1); 

INSERT INTO `Rents` (`id`, `out_date`, `in_date`, `car_id`) VALUES (1, '2022-07-14', '2022-07-15', 1); 
INSERT INTO `Rents` (`id`, `out_date`, `in_date`, `car_id`) VALUES (2, '2022-07-16', NULL, 1); 
INSERT INTO `Rents` (`id`, `out_date`, `in_date`, `car_id`) VALUES (3, '2022-07-16', NULL, 2); 
INSERT INTO `Rents` (`id`, `out_date`, `in_date`, `car_id`) VALUES (4, '2022-07-13', '2022-07-14', 3); 
INSERT INTO `Rents` (`id`, `out_date`, `in_date`, `car_id`) VALUES (5, '2022-07-16', NULL, 5); 

解决方案 #

由于MySQL 5.6不支持窗口函数，可通过子查询先获取每辆车的最新租赁日期，再关联到对应的租赁记录，最终得到目标结果：

SELECT 
    c.id,
    c.plate,
    c.flag,
    r.id,
    r.out_date,
    r.in_date,
    r.car_id,
    CASE
        WHEN r.id IS NULL THEN 1
        WHEN r.in_date IS NOT NULL THEN 1
        ELSE 0
    END AS status
FROM Cars c
LEFT JOIN (
    -- 子查询：获取每辆车的最新租赁日期
    SELECT car_id, MAX(out_date) AS latest_out_date
    FROM Rents
    GROUP BY car_id
) latest_r ON c.id = latest_r.car_id
-- 关联到对应最新日期的租赁记录
LEFT JOIN Rents r ON c.id = r.car_id AND r.out_date = latest_r.latest_out_date
WHERE c.flag = 1;

逻辑说明 #

1. 子查询latest_r按car_id分组，找出每辆车的最大out_date（即最新租赁日期）
2. 将Cars表与latest_r左连接，确保所有flag=1的车辆都被保留
3. 再将结果与Rents表左连接，匹配条件为车辆id相同且租赁日期等于最新日期，确保每辆车仅关联最新的那条租赁记录
4. 通过CASE语句计算状态：无租赁记录或最新租赁已归还则为1（可用），否则为0（不可用）

执行该SQL后，即可得到符合期望的结果。

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