sql - 过滤Oracle中重复的列

Question

我只想使用下面的 sql 查看一次相同的 pid(或 person_info 表中的 id)。最后添加“按 pid 分组”在 MySQL 中有效，但在 Oracle 中无效，因为我必须在选择后为每个分组依据。 Distinct 在这里也不起作用，因为它返回唯一组合而不仅仅是 pid。这让我发疯...提前致谢!

select pi.id                 pid,
   n.id                  nid,
   n.match_mode,
   n.match_method,
   n.match_gene_type,
   n.notification_status,
   pi.person_name,
   pi.id_card_no,
   pi.race
from notification n, person_info pi
where (n.src_data_id = pi.id or n.match_data_id = pi.id)
and n.match_mode = '1'
and n.match_gene_type = '1'
and n.notification_status = '2'
and pi.id_card_no IS NOT NULL

Answer 1

使用窗口函数row_number()

with cte as
(
select pi.id                 pid,
   n.id                  nid,
   n.match_mode,
   n.match_method,
   n.match_gene_type,
   n.notification_status,
   pi.person_name,
   pi.id_card_no,
   pi.race,
   row_number() over(partition by pi.id order by pi.id) as rn
from notification n, person_info pi
where (n.src_data_id = pi.id or n.match_data_id = pi.id)
and n.match_mode = '1'
and n.match_gene_type = '1'
and n.notification_status = '2'
and pi.id_card_no IS NOT NULL
) select * from cte where rn=1