MySQL中如何从两表获取每个产品最近两次销售的产品与客户详情

要实现每个产品最近两次采购记录的查询，窗口函数ROW_NUMBER() 是最直接的方案，Union语句确实无法实现分组内的条数限制，具体实现如下：

假设表结构 #

先明确两张表的基础字段（如果你的字段名不同，对应替换即可）：

• Products表：ProductID（主键）、ProductName
• Sales表：ProductID（外键关联Products）、Qty、CustID、CustName、SaleDate（采购日期）

通用SQL实现（支持MySQL 8.0+、SQL Server、PostgreSQL等） #

SELECT p.ProductName, s.ProductID, s.Qty, s.CustName, s.CustID, s.SaleDate
FROM (
    SELECT 
        ProductID, 
        Qty, 
        CustName, 
        CustID, 
        SaleDate,
        -- 按产品分组，采购日期倒序编号，最近的记录编号为1
        ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY SaleDate DESC) AS rn
    FROM Sales
) s
JOIN Products p ON s.ProductID = p.ProductID
WHERE s.rn <= 2 -- 筛选每个产品的前2条记录
ORDER BY s.ProductID, s.SaleDate DESC; -- 按产品ID、采购日期倒序排序

关键逻辑说明 #

1. 子查询中用ROW_NUMBER()给每个产品的销售记录编号：
   • PARTITION BY ProductID：按产品ID分组，确保编号是每个产品内部独立计算
   • ORDER BY SaleDate DESC：按采购日期倒序，最近的记录排在最前面，编号为1
2. 外层查询筛选rn <= 2，得到每个产品最近两次的采购记录
3. 关联Products表获取产品名称，最后按产品ID和日期排序

旧版MySQL（低于8.0）的替代方案 #

如果你的MySQL版本不支持窗口函数，可以用自关联的方式实现：

SELECT p.ProductName, s1.ProductID, s1.Qty, s1.CustName, s1.CustID, s1.SaleDate
FROM Sales s1
JOIN Products p ON s1.ProductID = p.ProductID
WHERE (
    SELECT COUNT(*) 
    FROM Sales s2 
    WHERE s2.ProductID = s1.ProductID AND s2.SaleDate >= s1.SaleDate
) <= 2
ORDER BY s1.ProductID, s1.SaleDate DESC;

这个逻辑是统计每个销售记录在同产品中比它日期新（或相同）的记录数量，数量<=2的就是最近两次的记录。

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