GROUP BY仅含少量列时如何选取MAX(id)并解决ORA-00979错误

嘿，这个问题我太熟悉了！咱们先搞清楚为什么会报错，再给你两种靠谱的解决方案。

为什么会报ORA-00979？ #

Oracle的GROUP BY规则很严格：SELECT列表里的非聚合函数列必须全部出现在GROUP BY子句中。你原来的SQL里，postindex AS postCode没有在GROUP BY里，所以触发了这个错误。但你不能直接把postindex加到GROUP BY里——因为你的示例数据里，同一个STREET_ID+HOUSENUMBER组合对应了两个不同的POSTCODE，加进去之后会把这两条记录分成两个组，就破坏了你要“唯一组合对应MAX(ID)”的需求了。

方案一：用窗口函数ROW_NUMBER()（推荐） #

这是处理这类“取分组内Top1”问题最简洁的方法，直接定位到每个组合里ID最大的那条完整记录：

SELECT id, street_id, housenumber, postcode
FROM (
    SELECT 
        h.id,
        h.street_id,
        h.housenumber,
        h.postindex AS postcode,
        -- 按street_id+housenumber分组，每组内按ID降序排，最大ID的记录rn=1
        ROW_NUMBER() OVER (
            PARTITION BY h.street_id, h.housenumber 
            ORDER BY h.id DESC
        ) AS rn
    FROM house h
    WHERE h.postindex IS NOT NULL AND h.street_id IS NOT NULL
) ranked_houses
WHERE rn = 1
ORDER BY 
    street_id, 
    CAST(REGEXP_REPLACE(REGEXP_REPLACE(housenumber, '(\-|\/)(.*)'), '\D+') AS NUMBER), 
    housenumber;

方案二：关联子查询获取最大ID对应的记录 #

如果你习惯用传统的JOIN方式，也可以先通过子查询拿到每个组合的最大ID，再关联回原表拿到对应的POSTCODE：

SELECT h.id, h.street_id, h.housenumber, h.postindex AS postcode
FROM house h
INNER JOIN (
    -- 先拿到每个street_id+housenumber组合的最大ID
    SELECT street_id, housenumber, MAX(id) AS max_id
    FROM house
    WHERE postindex IS NOT NULL AND street_id IS NOT NULL
    GROUP BY street_id, housenumber
) max_id_map 
    ON h.street_id = max_id_map.street_id 
    AND h.housenumber = max_id_map.housenumber 
    AND h.id = max_id_map.max_id
WHERE h.postindex IS NOT NULL AND h.street_id IS NOT NULL
ORDER BY 
    h.street_id, 
    CAST(REGEXP_REPLACE(REGEXP_REPLACE(h.housenumber, '(\-|\/)(.*)'), '\D+') AS NUMBER), 
    h.housenumber;

这两种方法都能得到你想要的结果：只显示11000000,20512120,22,04074这条记录，同时保留正确的POSTCODE。

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