Oracle SQL一对多关联：按指定分类获取去重关联结果

现有两张表table1和table2，table2的id是table1的外键，用来描述table1的数据。同一个table1的id对应table2多条记录，而且table2里除了veg、non-veg还有其他分类。现在需要每个id各返回一条veg和一条non-veg记录（desc字段随便选一条就行），但当前的关联SQL会返回大量重复记录，得解决这个问题。

当前使用的SQL #

select * from 
table1 t1
inner join t2 on
t1.id = t2.id and t2.category in ('veg','non-veg')

表结构 #

table1 #

id    amount     purchase_date
1     100        1/1/22
2     200        2/1/22
3     300        3/2/21

table2 #

id    amount     category      desc
1     100        veg           abc
1     100        veg           xyz
1     100        non-veg       def
1     100        non-veg       jkl
2     300        veg           abc
2     300        veg           xyz
2     300        non-veg       def
2     300        non-veg       jkl
2     300        hot-veg       bvn
2     300        mad-veg       lji

期望结果 #

id    amount    category
1     100       veg
1     100       non-veg
2     300       veg
2     300       non-veg

解决方案 #

当前SQL返回重复的原因是：同一个id+category组合在table2里有多条记录（比如id=1的veg有两条），关联后这些记录都会被查出来。下面是几种解决方法：

方法1：用DISTINCT去重 #

直接在查询中加上DISTINCT，同时只选择需要的字段（避免因为desc或purchase_date不同导致的重复）：

select distinct t1.id, t2.amount, t2.category
from table1 t1
inner join table2 t2 on t1.id = t2.id 
where t2.category in ('veg','non-veg')

方法2：用GROUP BY分组 #

按id和category分组，聚合其他字段（同组内amount值相同，用MAX或MIN都可以）：

select t1.id, max(t2.amount) as amount, t2.category
from table1 t1
inner join table2 t2 on t1.id = t2.id 
where t2.category in ('veg','non-veg')
group by t1.id, t2.category

方法3：用窗口函数指定取某条记录 #

如果需要指定取每个id+category下的某一条记录（比如按desc排序后的第一条），可以用ROW_NUMBER()窗口函数：

select id, amount, category
from (
    select t1.id, t2.amount, t2.category,
           row_number() over(partition by t1.id, t2.category order by t2.desc) as rn
    from table1 t1
    inner join table2 t2 on t1.id = t2.id 
    where t2.category in ('veg','non-veg')
) sub_query
where rn = 1

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