如何获取表中重复的记录？ [英] How can I get the records which are deuplicated in a table?

问题描述

如何获取表格中的重复记录？



例如表：

 ID |姓名|部门|性别
 ======================= 
  1  | ABC | DPT1 | M 
  2  | DEF | DPT2 | M 
  3  | ABC | DPT1 | F 
  4  | DEF | DPT2 | F 
  5  | MNO | DPT3 | M 
  6  | PQR | DPT1 | M 

结果：

姓名
 ==== 
 ABC 
 DEF 

谢谢，

Janes T

解决方案

< blockquote>试试这个： -

  SELECT 名称，Count（*） FROM  Table_Name 
  GROUP   BY  name  HAVING  COUNT（*）>  1  

  SELECT 名称，部门，性别
  FROM  tablename 
  GROUP   BY 姓名，部门，性别
  HAVING  COUNT（*）> 1 

如果您想获得unigue名称，则不需要任何聚合函数。使用DISTINCT语句。

  SELECT   DISTINCT  [name] 
  FROM  TableName 

如果您只想获得重复项，则需要使用聚合函数。请参阅TrushnaK的解决方案1.

  SELECT  [name] 
  FROM  TableName 
  GROUP   BY  [name] 
  HAVING  COUNT（[name]）> 1 

但是（！）......你可以看，你会得到重复的名字，无论是部门和性别。我不确定你真的想要它。

  SELECT  [ name]，[gender]，[dept] 
  FROM  TableName 
  GROUP  < span class =code-keyword> BY  [name]，[gender]，[dept] 
  HAVING  COUNT（[name]） > 1 
  ORDER   BY  [name]，[gender]，[dept] 

以上查询返回姓名及其性别和雇员雇用地点。

Hi,

How can I get the records which are deuplicated in a table?

Eg Table:

ID | Name |Dept |Gender
=======================
1  |ABC   |DPT1 |M
2  |DEF   |DPT2 |M
3  |ABC   |DPT1 |F
4  |DEF   |DPT2 |F
5  |MNO   |DPT3 |M
6  |PQR   |DPT1 |M

Result:

Name
====
ABC
DEF

Thanks,
Janes T

解决方案

try this:-

SELECT name,Count(*) FROM Table_Name
GROUP BY name HAVING COUNT(*) > 1

SELECT Name,Dept,Gender
FROM tablename
GROUP BY Name,Dept,Gender
HAVING COUNT(*)>1

If you would like to get unigue names, you don't need any aggregate function. Use DISTINCT statement.

SELECT DISTINCT [name]
FROM TableName

If you would like to get duplicates only, aggregate function is necessary. See solution 1 by TrushnaK.

SELECT [name]
FROM TableName
GROUP BY [name]
HAVING COUNT([name])>1

But (!)... as you can see, you will get duplicate names, no matter of department and gender. I'm not sure you really want it.

SELECT [name], [gender], [dept]
FROM TableName
GROUP BY [name], [gender], [dept]
HAVING COUNT([name])>1
ORDER BY [name], [gender], [dept]

Above query returns names with its gender and place of employee hire.

这篇关于如何获取表中重复的记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！