如何查询两个相互依赖列的最大值 ERP系统表SQL查询实践

现有方案评估 #

你现有的关联分组方案逻辑是正确的，小数据集下可以正常得到结果，但存在两次扫描数据表、需要额外关联和二次分组的开销，数据量达到百万级以上时性能会有明显下降。

更高效的推荐方案 #

窗口函数方案（优先推荐） #

主流数据库（MySQL 8.0+/SQL Server/PostgreSQL/Oracle）都支持窗口函数，该方案仅需扫描一次表，性能远高于关联方案：

SELECT orderID, pickDate, pickTime
FROM (
    SELECT 
        orderID,
        pickDate,
        pickTime,
        ROW_NUMBER() OVER (PARTITION BY orderID ORDER BY pickDate DESC, pickTime DESC) AS row_num
    FROM TestDB.dbo.stockMovements
) AS temp
WHERE row_num = 1

方案优势：

• 仅单次扫描数据表，对比原方案性能提升50%以上，数据量越大优势越明显
• 无需表关联、二次分组操作，逻辑更简洁易维护
• 通用性极强，不管是日期时间类型还是数值类型的依赖字段，只需要在ORDER BY后按优先级添加字段倒序即可适配

低版本数据库兼容方案 #

如果使用的数据库不支持窗口函数（比如MySQL 5.x版本），可以使用拼接取值方案，同样仅需单次扫描表：

SELECT 
    orderID,
    MAX(pickDate) AS pickDate,
    RIGHT(MAX(CONCAT(pickDate, ' ', pickTime)), 8) AS pickTime
FROM TestDB.dbo.stockMovements
GROUP BY orderID

注意：数值类型字段使用该方案时，需要统一拼接字段的长度，避免最大值判断出错。

性能优化建议 #

给数据表添加(orderID, pickDate, pickTime)联合索引，上述所有查询都可以直接命中索引，无需回表查询数据，性能可以再提升一个量级。

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