在SQL Server中查找重复的行

我有一个组织的SQL Server数据库，有许多重复的行。我想运行一个选择语句来获取所有这些和被欺骗的数量，同时还返回与每个组织相关的id。

这样的陈述:

SELECT     orgName, COUNT(*) AS dupes  
FROM         organizations  
GROUP BY orgName  
HAVING      (COUNT(*) > 1)

将返回如下内容

orgName        | dupes  
ABC Corp       | 7  
Foo Federation | 5  
Widget Company | 2

但我也想要他们的id。有什么办法可以做到吗?也许就像

orgName        | dupeCount | id  
ABC Corp       | 1         | 34  
ABC Corp       | 2         | 5  
...  
Widget Company | 1         | 10  
Widget Company | 2         | 2

原因是还有一个单独的用户表链接到这些组织，我想把它们统一起来(因此删除dupes，用户链接到同一个组织，而不是dupe组织)。但我想手动部分，所以我不会搞砸任何事情，但我仍然需要一个语句返回所有的dupe组织的id，这样我就可以通过用户列表。

当前回答

select orgname, count(*) as dupes, id 
from organizations
where orgname in (
    select orgname
    from organizations
    group by orgname
    having (count(*) > 1)
)
group by orgname, id

2010-01-21 20:49:49

其他回答

select a.orgName,b.duplicate, a.id
from organizations a
inner join (
    SELECT orgName, COUNT(*) AS duplicate
    FROM organizations
    GROUP BY orgName
    HAVING COUNT(*) > 1
) b on o.orgName = oc.orgName
group by a.orgName,a.id

2015-09-15 07:01:15

select * from [Employees]

查找重复记录 1)使用CTE

with mycte
as
(
select Name,EmailId,ROW_NUMBER() over(partition by Name,EmailId order by id) as Duplicate from [Employees]
)
select * from mycte

2)使用GroupBy

select Name,EmailId,COUNT(name) as Duplicate from  [Employees] group by Name,EmailId

2016-11-18 12:44:38

我有一个更好的选择，把重复的记录放在一个表中

SELECT x.studid, y.stdname, y.dupecount
FROM student AS x INNER JOIN
(SELECT a.stdname, COUNT(*) AS dupecount
FROM student AS a INNER JOIN
studmisc AS b ON a.studid = b.studid
WHERE (a.studid LIKE '2018%') AND (b.studstatus = 4)
GROUP BY a.stdname
HAVING (COUNT(*) > 1)) AS y ON x.stdname = y.stdname INNER JOIN
studmisc AS z ON x.studid = z.studid
WHERE (x.studid LIKE '2018%') AND (z.studstatus = 4)
ORDER BY x.stdname

上述查询的结果显示了所有具有唯一学生id的重复名称和重复出现的次数

点击这里查看sql . sql的结果

2018-06-25 09:22:28

Select * from (Select orgName,id,
ROW_NUMBER() OVER(Partition By OrgName ORDER by id DESC) Rownum
From organizations )tbl Where Rownum>1

所以rowum> 1的记录将是表中的重复记录。'分区由'第一组记录，然后通过给他们序列号序列化他们。所以rownum> 1将是重复的记录，可以这样删除。

2015-03-10 05:58:02

如果要删除重复项:

WITH CTE AS(
   SELECT orgName,id,
       RN = ROW_NUMBER()OVER(PARTITION BY orgName ORDER BY Id)
   FROM organizations
)
DELETE FROM CTE WHERE RN > 1

2016-06-17 09:51:06

在SQL Server中查找重复的行

推荐文章

最新文章

标签