如何先连接表再过滤行以保留每组最大值的SQL实现

问题拆解 #

• 关于LEFT JOIN的错误：你要的是有购买记录的英国访客，用LEFT JOIN会把没买过的访客也留着，而且如果把country='英国'写在JOIN的ON条件里，会导致非英国访客只要有购买记录也被保留，这就是过滤失效的原因。应该先从visitors表筛出英国用户，再用INNER JOIN关联有购买记录的activity_log。
• 取最后一次购买：用窗口函数ROW_NUMBER()按访客分组，购买时间倒序排，取每组第一条就行，这是最省心的方法。

正确SQL语句 #

SELECT visitor_id, what_was_purchased
FROM (
    SELECT 
        v.visitor_id,
        al.what_was_purchased,
        -- 按访客分组，购买时间倒序排名，最新的排第1
        ROW_NUMBER() OVER (PARTITION BY v.visitor_id ORDER BY al.activity_time DESC) AS purchase_rank
    FROM visitors v
    -- 用INNER JOIN确保只保留有购买记录的访客
    INNER JOIN activity_log al 
        ON v.visitor_id = al.visitor_id
    WHERE 
        v.country = '英国'
        -- 这里假设activity_log里有标识购买的字段，比如activity_type，根据实际表结构调整
        AND al.activity_type = '购买'
) AS ranked_purchases
-- 只留每组的第一条（最后一次购买）
WHERE purchase_rank = 1;

语句说明 #

1. 内层子查询：先从visitors表筛出英国用户，再关联他们的购买记录，同时给每条购买记录按访客分组、时间倒序排名。
2. 外层查询：只筛选排名为1的记录，也就是每个访客的最后一次购买。

兼容老数据库的替代方案（无窗口函数时用） #

如果你的数据库不支持窗口函数，用子查询先找出每个访客的最后购买时间，再关联获取对应记录：

SELECT v.visitor_id, al.what_was_purchased
FROM visitors v
INNER JOIN activity_log al 
    ON v.visitor_id = al.visitor_id
-- 关联子查询得到每个访客的最后购买时间
INNER JOIN (
    SELECT visitor_id, MAX(activity_time) AS last_purchase_time
    FROM activity_log
    WHERE activity_type = '购买'
    GROUP BY visitor_id
) AS last_purchase_info
    ON al.visitor_id = last_purchase_info.visitor_id
    AND al.activity_time = last_purchase_info.last_purchase_time
WHERE v.country = '英国';

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