MySQL三表关联查询：筛选无有效预订的当前客户（MAX日期处理）

表结构说明 #

• BookingDetails：存储客户信息，共140,000条记录，字段包含CustomerRef、Name、Expiry
• BookingRequest：存储客户预订请求，共83,000条记录，字段包含ID、CustomerRef
• RequestLog：存储预订请求状态日志，共110,000条记录，字段包含ID、RequestID、DateAdded、Status

需求 #

筛选满足以下两个条件的客户：

1. 客户有效期大于当前日期（DATE(Expiry) > CURDATE()）
2. 该客户的所有预订请求的最新状态均不为Booked

原查询问题 #

现有查询语句在客户仅存在一条预订请求时结果正确，但当客户同时存在“最新状态为Booked”和“最新状态为非Booked”的请求时（例如CustomerRef=1的John），会错误将该客户纳入结果集。

原查询语句：

SELECT BookingDetails.CustomerRef,  BookingDetails.Name, BookingDetails.Expiry, b.Status AS Holidaystatus
FROM BookingDetails
JOIN BookingRequest ON BookingRequest.CustomerRef=  BookingDetails.CustomerRef
INNER JOIN RequestLog a ON a.RequestID=BookingRequest.ID
JOIN (SELECT MAX(DateAdded)  maxdate, RequestID FROM RequestLog GROUP BY RequestID) AS b ON  a.RequestID=b.RequestID AND a.DateAdded=b.maxdate WHERE a.Status != 'Booked' AND DATE(BookingDetails.Expiry) > CURDATE()
GROUP BY  BookingDetails.CustomerRef

期望结果仅包含Sarah（CustomerRef=2）和Fred（CustomerRef=3），但当前结果错误包含John。

修正方案 #

方案1：排除存在Booked状态请求的客户 #

先找出所有存在至少一条预订请求最新状态为Booked的客户，再从有效客户中排除这些客户：

SELECT bd.CustomerRef, bd.Name, bd.Expiry
FROM BookingDetails bd
WHERE DATE(bd.Expiry) > CURDATE()
AND bd.CustomerRef NOT IN (
    -- 筛选出有请求最新状态为Booked的客户
    SELECT DISTINCT br.CustomerRef
    FROM BookingRequest br
    JOIN (
        -- 为每个请求标记最新状态的记录（rn=1即为最新）
        SELECT RequestID, Status,
               ROW_NUMBER() OVER(PARTITION BY RequestID ORDER BY DateAdded DESC) AS rn
        FROM RequestLog
    ) rl ON br.ID = rl.RequestID
    WHERE rl.rn = 1 AND rl.Status = 'Booked'
)
-- 可选：若需确保客户至少有一条预订请求，添加此条件
AND bd.CustomerRef IN (SELECT DISTINCT CustomerRef FROM BookingRequest)

方案2：分组校验所有请求状态 #

先获取每个请求的最新状态，再按客户分组，校验该客户所有请求的最新状态中没有Booked：

SELECT bd.CustomerRef, bd.Name, bd.Expiry
FROM BookingDetails bd
JOIN BookingRequest br ON bd.CustomerRef = br.CustomerRef
JOIN (
    -- 为每个请求标记最新状态的记录
    SELECT RequestID, Status,
           ROW_NUMBER() OVER(PARTITION BY RequestID ORDER BY DateAdded DESC) AS rn
    FROM RequestLog
) rl ON br.ID = rl.RequestID
WHERE rl.rn = 1 AND DATE(bd.Expiry) > CURDATE()
GROUP BY bd.CustomerRef, bd.Name, bd.Expiry
-- 校验该客户所有请求的最新状态中Booked的数量为0
HAVING SUM(CASE WHEN rl.Status = 'Booked' THEN 1 ELSE 0 END) = 0

修正逻辑说明 #

原查询的核心问题是：只要客户存在任意一条请求的最新状态不为Booked，就会被筛选出来，完全忽略了该客户其他请求可能存在Booked状态的情况。

修正后的逻辑从两个角度解决问题：

1. 方案1直接排除所有存在Booked状态请求的客户，确保剩余客户的所有请求最新状态都不符合Booked
2. 方案2通过分组统计，确保客户的所有请求最新状态中没有Booked记录

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