使用MAX与UNION ALL分组取对应字段值的结果异常问题

原SQL的问题 #

你写的SQL没有按c进行分组，直接取全局的max(t.a)，同时未分组情况下选取t.b会导致数据库随机返回某一行的b值，这就是结果不符合预期的原因。

方案1：使用窗口函数（推荐，适用于支持窗口函数的数据库如MySQL8+、PostgreSQL、SQL Server等） #

通过ROW_NUMBER()窗口函数按c分组，每组内按a降序排序，取排名第一的记录，就能得到每组a最大值对应的b和c：

SELECT a, b, c
FROM (
    SELECT 
        a, b, c,
        ROW_NUMBER() OVER (PARTITION BY c ORDER BY a DESC) AS rn
    FROM (
        SELECT 10 as a,1 as b,1 as c 
        UNION ALL
        SELECT 20,2,1 
        UNION ALL
        SELECT 30,3,2  
        UNION ALL
        SELECT 40,4,2 
    ) AS t
) AS ranked_t
WHERE rn = 1
ORDER BY c;

如果同一c组内存在多个a值相同的最大值，想保留所有这类记录，把ROW_NUMBER()换成RANK()即可。

方案2：关联子查询（适用于不支持窗口函数的旧版本数据库） #

先通过子查询找出每个c组对应的a的最大值，再关联原表获取对应的b和c：

SELECT t.a, t.b, t.c
FROM (
    SELECT 10 as a,1 as b,1 as c 
    UNION ALL
    SELECT 20,2,1 
    UNION ALL
    SELECT 30,3,2  
    UNION ALL
    SELECT 40,4,2 
) AS t
INNER JOIN (
    SELECT c, MAX(a) AS max_a
    FROM (
        SELECT 10 as a,1 as b,1 as c 
        UNION ALL
        SELECT 20,2,1 
        UNION ALL
        SELECT 30,3,2  
        UNION ALL
        SELECT 40,4,2 
    ) AS t
    GROUP BY c
) AS max_t ON t.c = max_t.c AND t.a = max_t.max_a
ORDER BY c;

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