MySQL使用LEFT JOIN结合MAX(date)查询各商品最新销售明细方案咨询

现有表结构 #

三个表结构如下：

Product 
   Id
Sale
   Id
   Date
SaleDetails
   Id
   IdSale
   IdProduct
   QTE

样例数据 #

Product表 #

Sale表 #

SaleDetails表 #

查询需求 #

使用LEFT JOIN为每个Product查询满足以下条件的记录，无销售记录的字段返回Null：

• 取该商品的最新销售日期对应的Sale记录
• 同销售单（同IdSale）下存在多条同商品的SaleDetail记录时，取SaleDetail.Id最大的那条

预期结果 #

原有查询的问题 #

原查询存在两个核心错误：

1. 分组逻辑错误，仅按Sale.date分组，没有按商品维度分组，无法拿到每个商品的最新销售日期
2. 没有处理同销售单下取最大SaleDetail.Id的逻辑，直接取Qte会出现取值错误

正确实现方案 #

方案1：使用窗口函数（兼容性好，主流数据库MySQL8.0+/PostgreSQL/SQL Server都支持） #

SELECT 
    p.Id AS IdProduct,
    sd.Qte,
    sd.Id AS `SaleDetail.Id`,
    sd.IdSale,
    s.Date AS `Sale.Date`
FROM Product p
LEFT JOIN (
    SELECT 
        sd.*,
        s.Date,
        -- 按商品分组，先按销售日期倒序，再按SaleDetailId倒序，排名第一的就是目标记录
        ROW_NUMBER() OVER (PARTITION BY sd.IdProduct ORDER BY s.Date DESC, sd.Id DESC) AS rn
    FROM SaleDetails sd
    JOIN Sale s ON sd.IdSale = s.Id
) sd ON p.Id = sd.IdProduct AND sd.rn = 1
ORDER BY p.Id;

方案2：不使用窗口函数（兼容低版本MySQL等不支持窗口函数的场景） #

SELECT 
    p.Id AS IdProduct,
    sd.Qte,
    sd.Id AS `SaleDetail.Id`,
    sd.IdSale,
    s.Date AS `Sale.Date`
FROM Product p
LEFT JOIN (
    -- 先拿到每个商品最新销售日期对应的最大SaleDetailId
    SELECT 
        sd1.IdProduct,
        MAX(sd1.Id) AS max_sd_id
    FROM SaleDetails sd1
    JOIN Sale s1 ON sd1.IdSale = s1.Id
    WHERE (sd1.IdProduct, s1.Date) IN (
        -- 先拿到每个商品的最新销售日期
        SELECT sd2.IdProduct, MAX(s2.Date)
        FROM SaleDetails sd2
        JOIN Sale s2 ON sd2.IdSale = s2.Id
        GROUP BY sd2.IdProduct
    )
    GROUP BY sd1.IdProduct
) t ON p.Id = t.IdProduct
LEFT JOIN SaleDetails sd ON t.max_sd_id = sd.Id
LEFT JOIN Sale s ON sd.IdSale = s.Id
ORDER BY p.Id;

两个方案执行结果都和预期完全一致。

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