Oracle SQL使用DISTINCT后item_name列仍重复如何解决？

问题根源 #

• 原SQL的GROUP BY字段包含tb1.contract_id和tb1.amount，相当于按照「item_name + contract_id + amount」的组合做分组，同一个item_name下不同合同自然会生成多行结果，此处加DISTINCT也无法生效，因为不同contract_id的行本身就是完全独立的不同行。
• 原语句GROUP BY包含tb1.amount，导致你写的max(tb1.amount)等价于直接取tb1.amount，完全没有统计最大值的效果。
• 子查询逻辑冗余，不需要额外关联已经统计过的临时表，用Oracle支持的窗口函数即可高效实现需求。

推荐解决方案（窗口函数实现） #

SELECT contract_id, item_name, max_amount
FROM (
    SELECT 
        tb1.contract_id,
        tb2.item_name,
        tb1.amount AS max_amount,
        -- 按item_name分组，组内按金额倒序排序，排名第1的就是每个item对应最高金额的行
        ROW_NUMBER() OVER(PARTITION BY tb2.item_name ORDER BY tb1.amount DESC) AS rn
    FROM main_table tb1
    INNER JOIN item tb2 
        ON tb1.item_detail_id = tb2.item_detail_id
) temp
WHERE rn = 1;

如果需要保留同一个item_name下多个并列最高金额的行，把ROW_NUMBER()替换为RANK()即可。

备选方案（适配低版本Oracle不支持窗口函数的场景） #

SELECT 
    tb1.contract_id,
    tb2.item_name,
    tb1.amount AS max_amount
FROM main_table tb1
INNER JOIN item tb2 
    ON tb1.item_detail_id = tb2.item_detail_id
WHERE (tb2.item_name, tb1.amount) IN (
    SELECT tb2_inner.item_name, MAX(tb1_inner.amount)
    FROM main_table tb1_inner
    INNER JOIN item tb2_inner 
        ON tb1_inner.item_detail_id = tb2_inner.item_detail_id
    GROUP BY tb2_inner.item_name
);

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