SQL如何查询每个订单对应最大Ship_num的所有行数据

问题原因 #

你原来的SQL写法逻辑有问题：你将item_code、qty_to_pick等非分组维度字段都加入了GROUP BY，相当于按照所有字段的组合拆分分组，取到的max(ship_num)只是每个拆分后小分组的最大值，而非每个Order_ID维度下的全局最大Ship_num，自然得不到正确结果。

正确实现方案 #

提供两种主流兼容的写法，你可以根据自己使用的数据库选择：

方案1：子查询关联（兼容所有主流数据库） #

先通过子查询查出每个Order_ID对应的最大Ship_num，再关联回原表匹配所有对应行：

SELECT t1.*
FROM table1 t1
INNER JOIN (
    SELECT Order_ID, MAX(Ship_num) AS max_ship_num
    FROM table1
    GROUP BY Order_ID
) t2 
ON t1.Order_ID = t2.Order_ID AND t1.Ship_num = t2.max_ship_num

方案2：窗口函数（支持MySQL8.0+、PostgreSQL、SQL Server 2008+、Oracle等新版本数据库） #

用RANK窗口函数给每个Order_ID下的行按Ship_num倒序排名，筛选出排名为1的所有行即可：

WITH ranked_table AS (
    SELECT *,
        RANK() OVER(PARTITION BY Order_ID ORDER BY Ship_num DESC) AS rank_num
    FROM table1
)
SELECT Order_ID, Ship_num, Item_code, Qty_to_pick, Qty_picked, Pick_date
FROM ranked_table
WHERE rank_num = 1

这里用RANK而非ROW_NUMBER是为了适配同一个Order_ID存在多个相同最大Ship_num的场景，确保所有符合条件的行都能被筛选出来。

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