LEFT JOIN关联表查询最新购买记录时日期筛选条件失效如何解决

• 日期筛选条件放错了位置：你把s1.date <= '2021-10-26 23:59:59'写在了LEFT JOIN的ON子句中，这个条件只会用来判断s2的记录是否满足匹配要求，不会过滤s1的原始数据，因此s1中所有符合其他WHERE条件的记录都会被返回，看起来就像日期筛选被忽略了。
• 逻辑匹配错误：你要找的是截止到指定时间的最新记录，核心逻辑是先把所有购买记录限制在该时间点之前，再从中找最新的那条。原写法把日期限制加在关联条件里，完全没有起到过滤原始记录的作用。
• 额外语法漏洞：原SQL中的日期值、client_id = c001没有加单引号，部分数据库会出现隐式转换错误。

方案1：保留原有左连接求最大值的逻辑，调整条件位置 #

SELECT  s1.date, s1.client_id, s1.package_id 
FROM purchaseHistory s1 
LEFT JOIN purchaseHistory s2 ON s1.date < s2.date 
                            AND s1.client_id = s2.client_id
                            AND s2.date <= '2021-10-26 23:59:59' -- 限制s2的记录也在截止时间内
WHERE  s1.released = '1'
  AND s1.verified = '1'
  AND s2.client_id IS NULL
  AND s1.client_id = 'c001'
  AND s1.date <= '2021-10-26 23:59:59' -- 限制s1的记录在截止时间之前

方案2：使用窗口函数，逻辑更清晰不易出错 #

如果你的数据库支持窗口函数，用ROW_NUMBER()写法可读性更高，也不容易出现条件位置错误：

SELECT date, client_id, package_id
FROM (
    SELECT 
        date, client_id, package_id,
        ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY date DESC) AS rn
    FROM purchaseHistory
    WHERE released = '1'
      AND verified = '1'
      AND client_id = 'c001'
      AND date <= '2021-10-26 23:59:59'
) t
WHERE rn = 1

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