GROUP BY查询中如何使用SELECT聚合函数获取销售单最新状态

现有3张业务数据表，分别为Sale、SaleStatus、SaleStatusType，表结构定义如下：

SaleStatus（销售状态流水表）:
   SaleStatusID
   SaleStatusTypeID
   CreateDate
   SaleID

SaleStatusType（销售状态字典表）:
   SaleStatusTypeID
   SaleStatusTypeName

Sale（销售主表）:
   SaleID
   <其他销售业务字段>

业务规则：单个SaleID可对应多条SaleStatus流水记录。需求为基于DATETIME类型的CreateDate字段，查询每个saleID及其最新入库的SaleStatus关联信息。
原编写的SQL存在语法、逻辑错误，执行时报错，原SQL如下：

SELECT SST.SaleStatusType, SS.SaleStatusTypeID, SS.SaleID
FROM SaleStatusType AS SST
   INNER JOIN SaleStatus AS SS 
      ON SS.SaleStatusTypeID = SST.SaleStatusTypeID
GROUP BY S.SaleID
HAVING SS.CreateDate = MAX(SS.CreateDate)

• 别名引用错误：GROUP BY子句中使用的表别名S未在查询中定义，查询仅给SaleStatusType、SaleStatus分别定义了别名SST、SS，未引入别名S对应的表
• 字段名错误：SELECT子句中引用的SST.SaleStatusType不存在，对应字段实际名称为SaleStatusTypeName
• 聚合逻辑错误：HAVING子句中直接引用非聚合字段SS.CreateDate和聚合值MAX(SS.CreateDate)做匹配不符合SQL语法，且分组后无法直接定位到最大CreateDate对应的整条状态记录。

方案1：关联聚合子查询（兼容所有SQL版本） #

先按SaleID分组算出每个销售单对应的最大状态创建时间，再关联回原表拿到对应状态的完整信息，需要关联销售主表字段时直接追加JOIN即可：

SELECT 
    SS.SaleID,
    SS.SaleStatusID,
    SS.SaleStatusTypeID,
    SST.SaleStatusTypeName,
    SS.CreateDate AS LatestStatusTime
FROM SaleStatus SS
INNER JOIN SaleStatusType SST
    ON SS.SaleStatusTypeID = SST.SaleStatusTypeID
INNER JOIN (
    SELECT
        SaleID,
        MAX(CreateDate) AS MaxCreateTime
    FROM SaleStatus
    GROUP BY SaleID
) latest
    ON SS.SaleID = latest.SaleID
    AND SS.CreateDate = latest.MaxCreateTime

注意：如果同一个SaleID下存在多条CreateDate完全相同的状态记录，该方案会返回所有匹配的同时间记录。

方案2：窗口函数写法（支持MySQL8.0+、PostgreSQL、SQL Server等主流数据库新版本，性能更优） #

用窗口函数按SaleID分区、按创建时间倒序打排名标记，直接取排名为1的记录即可，可灵活控制重复时间的返回规则：

SELECT
    SaleID,
    SaleStatusID,
    SaleStatusTypeID,
    SaleStatusTypeName,
    CreateDate AS LatestStatusTime
FROM (
    SELECT
        SS.SaleID,
        SS.SaleStatusID,
        SS.SaleStatusTypeID,
        SST.SaleStatusTypeName,
        SS.CreateDate,
        ROW_NUMBER() OVER (PARTITION BY SS.SaleID ORDER BY SS.CreateDate DESC) AS rn
    FROM SaleStatus SS
    INNER JOIN SaleStatusType SST
        ON SS.SaleStatusTypeID = SST.SaleStatusTypeID
) t
WHERE rn = 1

如果需要保留同一SaleID下创建时间完全一致的多条最新状态，把ROW_NUMBER()替换为RANK()即可。

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