oracle partition by 查询重复记录中的1条数据(获取表去重后的数据所有字段)

1，partition by 分组后给分组数据排序

select t.*,row_number() over(partition by t."name",t."rid" order by t."rid") as "sort" from "person" t;

2、获取去重后的记录

select t2.* from (SELECT t.*,row_number() over(partition by t."name",t."rid" order by t."rid") as "sort" FROM "person" t) t2 where t2."sort" = 1;

3、group by 和 partition by 在去重时适用的场景

3.1、 group by 适用于 当重复的数据只有部分字段是一样的，有些字段不一样，比如id是自增的，那么就可以配合max()或者min()来去重

select t2.* from "person" t2,
(select t."name",t."rid",MAX(t."id") as maxId from "person" t group by t."name",t."rid") t3
where t2."id" = t3.MAXID;

3.2、 partition  by 适用于 当重复的数据所有字段都一样时