如何查询每个客户最后购买的商品？（含表结构及错误SQL示例）

表结构 #

• invoice表：id（发票ID）、client_id（客户ID）
• items表：id（商品条目ID）、invoice_id（关联发票ID）、name（商品名称）、date（购买日期）

需求 #

获取每个客户的client_id，以及该客户最后一次购买对应的商品name和date。

原查询的问题 #

你写的查询无法得到正确结果，原因是：

• GROUP BY i.client_id后，ii.name既不在分组字段中，也没有使用聚合函数，数据库会返回该分组下随机的一条name值，无法保证和MAX(ii.date)对应同一条购买记录。
• 仅用MAX(ii.date)只能拿到客户的最晚购买日期，但无法关联到该日期对应的商品名称。

正确解法 #

解法1：窗口函数（推荐，支持MySQL 8+、PostgreSQL、SQL Server等） #

利用ROW_NUMBER()窗口函数按客户分组，将每个客户的购买记录按日期倒序排序，取排序第一的记录就是最后一次购买：

SELECT client_id, name, date
FROM (
    SELECT 
        i.client_id, 
        ii.name, 
        ii.date,
        -- 按客户分组，日期倒序排序，每条记录获得一个序号
        ROW_NUMBER() OVER (PARTITION BY i.client_id ORDER BY ii.date DESC) AS rn
    FROM invoice i
    INNER JOIN items ii ON i.id = ii.invoice_id
) t
-- 取每个客户的第一条（最晚日期）记录
WHERE rn = 1;

如果一个客户在同一天有多个购买记录，需要保留所有这些记录，可以把ROW_NUMBER()换成RANK()。

解法2：子查询关联（兼容旧版数据库） #

先通过子查询找出每个客户的最晚购买日期，再关联原表获取对应的商品信息：

SELECT 
    i.client_id, 
    ii.name, 
    ii.date
FROM invoice i
INNER JOIN items ii ON i.id = ii.invoice_id
-- 匹配每个客户的最晚购买日期
WHERE (i.client_id, ii.date) IN (
    SELECT 
        i2.client_id, 
        MAX(ii2.date)
    FROM invoice i2
    INNER JOIN items ii2 ON i2.id = ii2.invoice_id
    GROUP BY i2.client_id
);

这个方法会返回客户最晚购买日期对应的所有商品记录（如果同一天有多条）。

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