如何按WONo、AssortSLNo分组获取TL最大值对应的唯一SecID行

问题说明 #

需要基于WONo、AssortSLNo、TL列获取唯一的SecID行，规则为：按WONo和AssortSLNo分组，提取每组中TL值最大的那一行。

原始数据 #

SecID    WONo   AssortSLNo     TL   
11       46       1            35976    
12       46       1            34593    
9        46       3            14639    
23       46       1            12761    
87       46       3            34738    
74       46       2            30575    
2        46       1            29952    
67       46       2             8993    

期望结果 #

SecID   WONo    AssortSLNo       TL 
11      46         1           35976    Highest Value
74      46         2           30575    
87      46         3           34738    

解决方案 #

以下是几种主流数据库的实现方式：

方法1：窗口函数（适用于MySQL 8.0+、PostgreSQL、SQL Server等） #

利用ROW_NUMBER()窗口函数给分组内的行按TL降序编号，筛选编号为1的行即可：

SELECT SecID, WONo, AssortSLNo, TL
FROM (
    SELECT 
        SecID, WONo, AssortSLNo, TL,
        ROW_NUMBER() OVER (PARTITION BY WONo, AssortSLNo ORDER BY TL DESC) AS rn
    FROM your_table_name
) t
WHERE rn = 1;

• PARTITION BY WONo, AssortSLNo：按指定字段分组
• ORDER BY TL DESC：组内按TL从大到小排序
• rn = 1：取组内第一行（即TL最大的行）

方法2：关联子查询（兼容低版本MySQL等不支持窗口函数的数据库） #

先通过子查询获取每组的最大TL值，再关联原表匹配对应行：

SELECT t1.SecID, t1.WONo, t1.AssortSLNo, t1.TL
FROM your_table_name t1
INNER JOIN (
    SELECT WONo, AssortSLNo, MAX(TL) AS max_tl
    FROM your_table_name
    GROUP BY WONo, AssortSLNo
) t2 ON t1.WONo = t2.WONo 
    AND t1.AssortSLNo = t2.AssortSLNo 
    AND t1.TL = t2.max_tl;

若同一组存在多个TL值相同的最大值，该方法会返回所有符合条件的行；需唯一行可结合DISTINCT或额外排序条件。

方法3：MySQL专属分组拼接法 #

利用GROUP_CONCAT和SUBSTRING_INDEX提取对应SecID：

SELECT 
    SUBSTRING_INDEX(GROUP_CONCAT(SecID ORDER BY TL DESC), ',', 1) AS SecID,
    WONo,
    AssortSLNo,
    MAX(TL) AS TL
FROM your_table_name
GROUP BY WONo, AssortSLNo;

注意：若SecID为数值类型，需转换结果类型；GROUP_CONCAT有长度限制，数据量大时不建议使用。

内容的提问来源于stack exchange，提问作者Shemul Kanti Nath