sql - 如何同时选择 row_number 和 count over partition？

Question

我需要找到重复记录(带有主记录 ID 和重复记录 ID):

select ciid, name from (
select ciid, name, row_number() over (
  partition by related_id, name order by updatedate desc) rn
) where rn = 1;

这给了我主记录 ID，但它也包括没有重复的记录。

如果我使用

select ciid, name from (
select ciid, name, row_number() over (
  partition by related_id, name order by updatedate desc) rn
) where rn > 1;

这让我得到所有重复的记录，但不是主记录。

我希望如果我做这样的事情:

select ciid, name from (
select ciid, name, row_number()  over (
    partition by related_id, name order by updatedate desc
  ) rn, count(*)  over (
    partition by related_id, name order by updatedate desc
  ) cnt
) where rn = 1 and cnt > 1;

但是我担心性能，甚至它实际上是否在做我想要的。

如何仅获取重复项的主记录？请注意 name不是唯一的列。只有 ciid是独特的。

Answer 1

我最终在我的问题中使用了类似的查询:

select ciid, name from (
select ciid, name, row_number()  over (
    partition by related_id, name order by updatedate desc
  ) rn, count(*)  over (
    partition by related_id, name desc
  ) cnt
) where rn = 1 and cnt > 1;

效果出奇的好。主记录是 rn = 1，重复是 rn > 1。确保 count(*) over (partition ..)不能有 order by条款。