MySQL 5.7.42：按客户查询最大pDate及对应最大id的数据

数据集 #

id,pDate,CustomerID,refNum
1,2023-07-04,1000,980
2,2023-07-05,1000,982
3,2023-07-05,1001,984
4,2023-07-05,1001,985

期望输出 #

id,pDate,CustomerID,refNum
2,2023-07-05,1000,982
4,2023-07-05,1001,985

尝试的查询语句 #

SELECT pDate, CustomerID,RefNum FROM table1 t1
JOIN (SELECT CustomerID, Max(pDate) as maxpdate FROM table1
GROUP BY CustomerID) sub on t1.CustomerID= sub.CustomerID AND t1.pDate = sub.maxdate

该语句返回多余记录的原因：仅筛选了每个CustomerID的最大pDate，但同一分组内可能存在多条记录共享这个最大pDate，缺少对id的二次筛选逻辑。

修改后的查询方案 #

方案1：嵌套子查询筛选最大id #

先筛选出每个CustomerID对应最大pDate的记录集合，再从中提取每个分组的最大id，最后关联原表获取完整数据：

SELECT t1.id, t1.pDate, t1.CustomerID, t1.refNum
FROM table1 t1
JOIN (
    SELECT CustomerID, MAX(id) AS maxid
    FROM table1
    WHERE (CustomerID, pDate) IN (
        SELECT CustomerID, MAX(pDate) AS maxpdate
        FROM table1
        GROUP BY CustomerID
    )
    GROUP BY CustomerID
) sub ON t1.id = sub.maxid;

方案2：使用NOT EXISTS关联子查询 #

直接筛选出不存在同CustomerID下pDate更大，或pDate相同但id更大的记录：

SELECT t1.id, t1.pDate, t1.CustomerID, t1.refNum
FROM table1 t1
WHERE NOT EXISTS (
    SELECT 1
    FROM table1 t2
    WHERE t2.CustomerID = t1.CustomerID
    AND (t2.pDate > t1.pDate OR (t2.pDate = t1.pDate AND t2.id > t1.id))
);

方案3：子查询同时获取最大pDate和对应最大id #

通过关联子查询先确定每个CustomerID的最大pDate，再在该日期范围内取最大id：

SELECT t1.id, t1.pDate, t1.CustomerID, t1.refNum
FROM table1 t1
JOIN (
    SELECT 
        CustomerID,
        MAX(pDate) AS maxpdate,
        MAX(id) AS maxid
    FROM table1 t2
    WHERE pDate = (SELECT MAX(pDate) FROM table1 WHERE CustomerID = t2.CustomerID)
    GROUP BY CustomerID
) sub ON t1.CustomerID = sub.CustomerID 
    AND t1.pDate = sub.maxpdate 
    AND t1.id = sub.maxid;

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