返回具有重复数据的行的ID [英] Return id of row that has duplicate data

问题描述

我需要获取重复的行的行ID

I am needing to get the row id of rows which have duplicate

Select Name from table1 group by Name having count(1) > 1

table1

ID | Name    | ClientID
----------------------------
01 | John    |  01
02 | Sam     |  01
03 | Sue     |  01
04 | John    |  02
05 | John    |  01

唯一的问题是，由于我在同一张表上有多个客户端，并且我不想将其他客户端的名称视为重复项，因此它只会返回名称"而不是列的ID.

the only problem is that it will only return the Name and not the ID of the column as I have multiple clients on the same table and I don't want to count the names from the other clients as duplicates.

有没有办法在表中查找重复的数据并返回行的ID，以便我可以在其他查​​询中使用这些ID?

So is there a way to find duplicate data in a table and return the ids of the rows so that I can then use those id's in another query?

我修改后的答案

谢谢大家给我的答案，这是我标记为答案的答案的修改.

Thank you every one for the answers I went with this one which is a modification of the one I marked as the answer.

select t1.*
from (select count(*) over (partition by entityname) as cnt ,t1.*
      from table1 t1 where ClientID = 1
     ) t1
where cnt > 1 and ClientID = 1 order by cnt;

推荐答案

使用窗口函数:

select t1.*
from (select t1.*, count(*) over (partition by name) as cnt
      from table1 t1
     ) t1
where cnt > 1;

count(*)(按名称划分)对每个名称的行数进行计数.但是，它是通过在每行上附加计数来实现的，而不是通过减少行数来实现的.这就是选择行所需的信息.

The count(*) over (partition by name) counts the number of rows for each name. However, it does this by appending the count on each row, not by reducing the number of rows. That's the information you need for selecting the rows.

这篇关于返回具有重复数据的行的ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！