SQL 2012在表中查找重复的列条目

I am using SQL 2012 and trying to identify rows where the SourceDataID column has two unique entries in the PartyCode column, and I'm having difficulties.

我正在使用SQL 2012并尝试识别SourceDataID列在PartyCode列中有两个唯一条目的行,并且我遇到了困难。

SELECT PartyCode, SourceDataID, count (*) as CNT
FROM CustomerOrderLocation (nolock) 
GROUP BY PartyCode, SourceDataID
HAVING (Count(PartyCode)>1)
ORDER BY PartyCode

Results are returning as such:

结果返回如下:

W3333 948_O 31 (party code/sourcedataid/CNT)

W3333 948_O 31(party code / sourcedataid / CNT)

This is showing me the total entries where the Partycode and the SourceDataID are listed together in the table. However, I need it to show a count of any instances where W333 lists 948_O as the SourceDataID more than once.

这向我显示了Partycode和SourceDataID在表中一起列出的总条目。但是,我需要它来显示W333多次将948_O列为SourceDataID的任何实例的计数。

I'm not having luck structuring the query to pull the results I am looking to get. How can I do this?

我没有运气构造查询来拉取我想要的结果。我怎样才能做到这一点?

2 个解决方案

#1

A CTE coupled with the PARTITION BY function is helpful in finding duplicates of this manner. Code below:

与PARTITION BY功能相结合的CTE有助于找到这种方式的重复。代码如下:

WITH CTE AS(
SELECT PartyCode, SourceDataID,
ROW_NUMBER()OVER(PARTITION BY SourceDataID ORDER BY SourceDataID) RN
FROM CustomerOrderLocation (NOLOCK))

SELECT * FROM CTE WHERE RN > 1

This should return every duplicate PartyCode attached to a SourceDataID.

这应该返回附加到SourceDataID的每个重复的PartyCode。

If you want to see the entire result, change the last SELECT statement to:

如果要查看整个结果,请将最后一个SELECT语句更改为:

SELECT * FROM CTE ORDER BY PartyCode, RN

#2

Thanks for the help everyone. I did not do the best job of describing the issue but this is the query I ended up creating to get my result set.

感谢大家的帮助。我没有尽力描述问题,但这是我最终创建的查询以获取我的结果集。

 ;with cte1 (sourcedataid, partycode) as (select sourcedataid, partycode from customerorderparty (nolock) group by PartyCode, SourceDataID)

select count(sourcedataid), sourcedataid from cte1 group by sourcedataid having count(sourcedataid) >1

#1