MariaDB带WHERE与EXISTS子查询结果不符 如何去除内部子查询

问题原因 #

1. 日期格式/类型不匹配：你的TravelDate字段是以DD-MM-YYYY格式存储的字符串，直接和curdate()返回的YYYY-MM-DD格式日期比较时，会按字符串规则逐位对比，导致判断完全不符合预期：比如08-24-2021作为字符串首位是0，远小于2021-08-18的首位2，会被误判为小于目标日期。这直接导致ID为10003的交易没有命中EXISTS条件被整体过滤，同时部分数据库的隐式转换异常也会导致10001的历史行程行丢失。
2. 保留字未转义：transaction是SQL保留字，作为表名使用时需要加转义符，否则部分数据库会执行报错或返回异常结果。

优化方案（去掉子查询） #

可以先筛选出所有符合「存在近5天及以后行程」的交易ID，再关联两张表获取全量数据，写法如下（以MySQL为例，其他数据库仅需修改日期转换函数即可）：

SELECT t.ID, t.Code, b.ID, b.TravelDate, b.Amount
FROM `transaction` t
JOIN Booking b ON b.TransactionID = t.ID
-- 关联符合条件的交易ID列表，替代EXISTS子查询
JOIN (
    SELECT DISTINCT TransactionID
    FROM Booking
    WHERE STR_TO_DATE(TravelDate, '%d-%m-%Y') >= CURDATE() - INTERVAL 5 DAY
) valid_t ON t.ID = valid_t.TransactionID
ORDER BY t.ID, STR_TO_DATE(b.TravelDate, '%d-%m-%Y');

如果你的数据库支持窗口函数，也可以用更简洁的写法：

SELECT ID, Code, BookingID, TravelDate, Amount
FROM (
    SELECT 
        t.ID, t.Code, 
        b.ID AS BookingID, b.TravelDate, b.Amount,
        -- 统计每个交易下符合条件的行程数量
        SUM(CASE WHEN STR_TO_DATE(b.TravelDate, '%d-%m-%Y') >= CURDATE() - INTERVAL 5 DAY THEN 1 ELSE 0 END) OVER (PARTITION BY t.ID) AS valid_cnt
    FROM `transaction` t
    JOIN Booking b ON b.TransactionID = t.ID
) t
WHERE valid_cnt > 0
ORDER BY ID, STR_TO_DATE(TravelDate, '%d-%m-%Y');

执行上述语句后即可得到你预期的结果：既返回ID为10001的所有行程，也返回ID为10003的对应行程。

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