在SQL中查找重复条目列 [英] Find Duplicate Entries Column in SQL

问题描述

我有这样的SQL表，

 COMPANY |类别|会议
 Wills Corp | 7 | 1 
 Wills Corp | 7 | 1 
 Wills Corp | 7 | 2 
 Stan Co | 2 | 1 
 Wills Corp | 5 | 1 
 Stan Co | 2 | 1 
 

---

为了清晰起见，PIEBALDconsult已添加此部分。



从表中选择明显的公司，类别，会话

公司类别会议
 Stan Co 2 1 
 Wills Corp 5 1 
 Wills Corp 7 1 
 Wills Corp 7 2 

---

类别和会话中有许多重复条目 COMPANY ..我想找到所有重复的条目<打击>类别或会话 COMPANY..i想要输出像这样..

公司|类别|会议
 Wills Corp | 7 | 1 
 Wills Corp | 7 | 2 
 Wills Corp | 5 | 1 

我尝试了以下查询并返回确切的公司名称，但我也希望分类和会话...

 选择 COMPANY，COUNT（*）来自（选择  Distinct  COMPANY，CATEGORY，Session 来自 表）Tbl  group   by  COMPANY 拥有 COUNT（*）>  1  

解决方案

这个怎么样：

  WITH  cte0  AS  
（
  SELECT  COMPANY，CATEGORY，Session  FROM  Dupes  GROUP   BY  COMPANY，CATEGORY，Session 
）
，cte1  AS  
（
  SELECT  COMPANY  FROM  cte0  GROUP   BY  COMPANY  HAVING  COUNT（*）>  1  
）
  SELECT  A. * 
  FROM  cte0 A 
  INNER   JOIN  cte1 B 
  ON  A.COMPANY = B.COMPANY 

问题是您无法通过有许多重复条目来描述您的意思。您实际上并没有在源表中查找重复项，一旦获得源中的唯一记录列表，您就会查找重复的公司值。也就是说，你的陈述结果重复从表中选择Distinct COMPANY，CATEGORY，Session

公司类别会议
 Stan Co 2 1 
 Wills Corp 5 1 
 Wills Corp 7 1 
 Wills Corp 7 2 

我使用公用表表达式（cte0）来保存结果。

http://technet.microsoft.com/en-us/library/ms190766(v=SQL.105).aspx [ ^ ]

（如果你愿意，你可以使用临时表。）



你指定的输出是公司结果的任何一行名称出现不止一次。



在cte1中，我选择了重复的公司名称。



最后，我加入了两个结果，只获得那些结果来自cte0的行，其名称多次出现。

尝试以下查询：

  SELECT 公司，类别，会话，COUNT（*） AS 出现
  FROM  TableName 
  GROUP   BY 公司，类别，会话
  HAVING  COUNT（*）> = 1 

显示每条记录的数量。



第二种方式：

  SELECT  * 
 < span class =code-keyword> FROM （
  SELECT 公司，类别，会话，ROW_NUMBER（） OVER （ PARTITION   BY 公司，类别 ORDER   BY 会话） AS  RowNo 
  FROM  TableName 
） AS  T 
  WHERE  T.RowNo = 1 

仅显示第一次出现记录;）



如果你想删除重复项，你需要更改这部分： WHERE 条件 RowNo> 1



SqlFiddle [ ^

I have SQL table like this,

COMPANY    | CATEGORY | Session
Wills Corp |   7      |  1
Wills Corp |   7      |  1
Wills Corp |   7      |  2
Stan Co    |   2      |  1
Wills Corp |   5      |  1
Stan Co    |   2      |  1

---

This section added for clarity by PIEBALDconsult.

Select Distinct COMPANY,CATEGORY,Session from Table

COMPANY    CATEGORY Session
Stan Co    2        1
Wills Corp 5        1
Wills Corp 7        1
Wills Corp 7        2

---

And there are Many Duplicate Entries in Category and Session COMPANY..I want to find all that have duplicates in either 'Category' or 'Session' COMPANY..i want Output Like this..

COMPANY    | CATEGORY | Session   
Wills Corp |   7      |  1
Wills Corp |   7      |  2
Wills Corp |   5      |  1

I tried the Following Query and it return exact Company name,but i want Category and Session also...

Select COMPANY,COUNT(*) from (Select Distinct COMPANY,CATEGORY,Session from Table) Tbl group by COMPANY having COUNT(*) > 1

解决方案

How about this:

WITH cte0 AS
(
	SELECT COMPANY , CATEGORY , Session FROM Dupes GROUP BY COMPANY , CATEGORY , Session 
)
, cte1 AS
(
	SELECT COMPANY FROM cte0 GROUP BY COMPANY HAVING COUNT(*) > 1
)
SELECT A.*
FROM cte0 A
INNER JOIN cte1 B
ON A.COMPANY=B.COMPANY

The problem is that you are having trouble describing what you mean by "there are Many Duplicate Entries". You are not actually looking for duplicates in the source table, you are looking for duplicate company values once you have a list of unique records from the source. That is, duplicates in the result of your statement Select Distinct COMPANY,CATEGORY,Session from Table

COMPANY    CATEGORY Session
Stan Co    2        1
Wills Corp 5        1
Wills Corp 7        1
Wills Corp 7        2

I used a Common Table Expression (cte0) to hold that result.
http://technet.microsoft.com/en-us/library/ms190766(v=SQL.105).aspx[^]
(You could use a temporary table if you like.)

The output you specified is any row from that result for which a company name appears more than once.

In cte1 I selected the duplicated Company name(s).

And finally I JOINed the two results to get only those rows from cte0 that have a name that appears more than once.

Try below query:

SELECT Company, Category, Session, COUNT(*) AS Occurence
FROM TableName
GROUP BY Company, Category, Session
HAVING COUNT(*)>=1

It shows the count of each record.

Second way:

SELECT *
FROM (
    SELECT Company, Category, Session, ROW_NUMBER() OVER(PARTITION BY Company, Category ORDER BY Session) AS RowNo
    FROM TableName
) AS T
WHERE T.RowNo=1

It shows only first occurence of record ;)

If you would like to delete duplicates, you need to change this part: WHERE condition to RowNo>1

SqlFiddle[^]

这篇关于在SQL中查找重复条目列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！