SQL 如何在SQL中查找重复记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3535012/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to find duplicate records in SQL?
提问by salvationishere
I am trying to develop a query to insert unique records but am receiving the SQL Server Primary Key error for trying to insert duplicate records. I was able to insert some values with this query but not for this record (score_14).
我正在尝试开发一个查询以插入唯一记录，但由于尝试插入重复记录而收到 SQL Server 主键错误。我能够使用此查询插入一些值，但不能插入此记录 (score_14)。
So now I am trying to find duplicate record with the following query. The challenge is that my PK is based on 3 columns: StudentID, MeasureDate, and MeasureID--all from a different table not mentioned below.
所以现在我正在尝试使用以下查询查找重复记录。挑战在于我的 PK 基于 3 列：StudentID、MeasureDate 和 MeasureID——所有这些都来自下面未提及的不同表。
But this only shows me count--instead I want to just return records with count > 1. How do I do that?
但这仅显示我计数 - 而我只想返回计数 > 1 的记录。我该怎么做？
select count(a.score_14) as score_count, A.studentid, A.measuredate, B.measurename+' ' +B.LabelName
from [J5C_Measures_Sys] A
join [J5C_ListBoxMeasures_Sys] B on A.MeasureID = B.MeasureID
join sysobjects so on so.name = 'J5C_Measures_Sys'
join syscolumns sc on so.id = sc.id
join [J5C_MeasureNamesV2_Sys] v on v.Score_field_id = sc.name
where so.type = 'u' and sc.name = 'score_14' and a.score_14 is not null
AND A.STUDENTID IS NOT NULL AND A.MEASUREDATE IS NOT NULL AND B.MEASURENAME IS NOT NULL
--and count(a.score_14)>1
group by a.studentid, a.measuredate, B.measurename, B.LabelName, A.score_14
having count(a.score_14) > 1
回答by OMG Ponies
Beth is correct - here's my re-write of your query:
贝丝是正确的 - 这是我对您的查询的重写：
SELECT a.studentid, a.measuredate, a.measureid
from [J5C_Measures_Sys] A
GROUP BY a.studentid, a.measuredate, a.measureid
HAVING COUNT(*) > 1
Previously:
之前：
SELECT a.studentid, a.measuredate, a.measureid
from [J5C_Measures_Sys] A
join [J5C_ListBoxMeasures_Sys] B on A.MeasureID = B.MeasureID
join sysobjects so on so.name = 'J5C_Measures_Sys'
AND so.type = 'u'
join syscolumns sc on so.id = sc.id
and sc.name = 'score_14'
join [J5C_MeasureNamesV2_Sys] v on v.Score_field_id = sc.name
where a.score_14 is not null
AND B.MEASURENAME IS NOT NULL
GROUP BY a.studentid, a.measuredate, a.measureid
HAVING COUNT(*) > 1
回答by Beth
you need to take A.score_14 out of your group by clause if you want to count it
如果你想计算它，你需要从 group by 子句中取出 A.score_14
