SQL查询获取客户最新订单及多表关联数据问题求解

问题根因 #

原有SQL的子查询使用TOP 1时未按客户维度分组，仅会返回全表时间最新的1条订单记录（对应CustomerID=1），因此其他客户关联时无法匹配到对应订单数据。

正确SQL写法 #

方案1：窗口函数版本（支持MySQL8.0+、PostgreSQL、SQL Server、Oracle等所有现代数据库）

SELECT 
    c.CustomerID,
    c.Name,
    s.SalePrice,
    ro.Date,
    ro.Amount
FROM Customer c
LEFT JOIN Sales s 
    ON c.CustomerID = s.ID
LEFT JOIN (
    SELECT 
        CustomerID,
        Date,
        Amount,
        -- 按客户分组，日期倒序排序，排序第一的就是该客户最新订单
        ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY STR_TO_DATE(Date, '%d-%m-%Y') DESC) AS rn
    FROM `Order`
) ro 
    ON c.CustomerID = ro.CustomerID 
    AND ro.rn = 1 -- 仅取每个客户的第一条最新订单

方案2：老版本数据库兼容方案（如MySQL5.x等不支持窗口函数的场景）

SELECT 
    c.CustomerID,
    c.Name,
    s.SalePrice,
    o.Date,
    o.Amount
FROM Customer c
LEFT JOIN Sales s 
    ON c.CustomerID = s.ID
LEFT JOIN `Order` o 
    ON c.CustomerID = o.CustomerID
    AND o.Date = (
        SELECT MAX(Date) 
        FROM `Order` o2 
        WHERE o2.CustomerID = c.CustomerID
    )

注意事项 #

1. Order是SQL保留关键字，作为表名使用时需要用反引号（MySQL）、双引号（PostgreSQL/Oracle）或方括号（SQL Server）包裹，避免语法报错。
2. 上述示例中STR_TO_DATE(Date, '%d-%m-%Y')是为了将字符串格式的dd-mm-yyyy日期转为日期类型排序，避免字符串排序逻辑错误。如果你的表中Date字段本身就是日期类型，直接写ORDER BY Date DESC即可。

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