使用MAX子查询遇平局时SQL主查询返回重复行的解决求助

首先纠正你原查询的一个错误：子查询里的rownum=1完全多余且逻辑错误。max(t3.load)本身就会返回当前id1对应的最大负载值，加rownum=1会让数据库先随机取一条t3记录再求最大值，这和你要的效果完全不符，先删掉这个条件。

你遇到的重复行问题，核心原因是t3表中同一个id1存在多条负载等于最大值的记录，直接关联t3时，每条符合条件的t3记录都会和t1、t2、t4关联一次，最终结果就会重复。下面给你几个实用的解决方法：

方法一：用DISTINCT直接去重（最简单） #

如果查询结果里重复行的所有字段完全一致，直接在select后加distinct就能快速去重：

select distinct t1.type, t2.loc, t3.load, t4.size
from t1
join t2 on t2.id1 = t1.id1
join t3 on t3.id1 = t1.id1
join t4 on t4.id2 = t1.id2
where t1.type = 'T'
  and t2.status = 'A'
  and t3.load = (select max(t3.load) 
                 from t3 
                 where t3.id1 = t1.id1);

方法二：先聚合t3的最大负载，再关联（从根源避免重复） #

先把t3按id1分组，提前算出每个id1的最大负载，再用这个聚合后的结果集关联其他表，每个id1只会对应一条记录，从根源消除重复：

select t1.type, t2.loc, t3_max.max_load, t4.size
from t1
join t2 on t2.id1 = t1.id1
join (
    -- 先聚合得到每个id1的最大负载
    select id1, max(load) as max_load
    from t3
    group by id1
) t3_max on t3_max.id1 = t1.id1
join t4 on t4.id2 = t1.id2
where t1.type = 'T'
  and t2.status = 'A';

方法三：用窗口函数筛选（灵活控制结果） #

如果t3还有其他字段需要展示，或者你想指定当有多个并列最大负载时取哪一条记录（比如最新的，假设t3有时间字段），可以用窗口函数ROW_NUMBER()：

select type, loc, load, size
from (
    select t1.type, t2.loc, t3.load, t4.size,
           -- 按id1分组，负载降序排序，给每组第一条记录标为1
           row_number() over (partition by t1.id1 order by t3.load desc) as rn
    from t1
    join t2 on t2.id1 = t1.id1
    join t3 on t3.id1 = t1.id1
    join t4 on t4.id2 = t1.id2
    where t1.type = 'T'
      and t2.status = 'A'
) t
where rn = 1;

如果想保留所有并列最大负载的记录，把ROW_NUMBER()换成RANK()即可，这样并列的记录会被标为同一个序号。

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