如何从表中删除重复数据？ [英] How to remove duplicate data from a table?

问题描述

我有一个表调用EMP，它有3列（EMP_NAME，EMP_MAIL，EMP_CONTACT）并且没有任何主键。现在我想使用SQL内联查询从这个表中删除重复行。



以下是示例。



emp_name emp_mail emp_contact



Arijita abcd @ xyz 8122020706

Arijit abc @ xy 8122020705

Arijit abc @ xy 8122020705

Arijit abc @ xy 8122020705



我搜索一个查询，这将帮助我删除重复值。



输出应为：



emp_name || emp_mail || emp_contact



Arijita || abcd @ xyz || 8122020706

Arijit || abc @ xy || 8122020705



我尝试了什么：



我发现了怎么样那里有很多重复。

解决方案

你可以为下面这样的东西写一个查询 -

;  WITH  CTE（emp_name，emp_mail，emp_contact，SlNo）
  AS  
（
  SELECT  emp_name，emp_mail，emp_contact，ROW_NUMBER（） OVER （ PARTITION   BY  emp_name，emp_mail，emp_contact  ORDER   BY  emp_name，emp_mail，emp_contact） AS  SlNo 
  FROM 演示
）
  DELETE   FROM  CTE 在哪里 SlNo>  1  

欲了解更多详情，请访问我的博客文章 -

SQL Server：删除/更新重复记录技术会谈 [ ^ ]



希望，它有帮助：）

看看这些链接

删除重复项SQL Server中的表 - 简单对话

删除SQL Server中的重复行



希望这些链接有帮助

; WITH CTE

AS

（

SELECT emp_name，emp_mail，emp_contact，ROW_NUMBER（）OVER（PARTITION BY emp_name，emp_mail，emp_contact ORDER BY emp_name，emp_mail，emp_contact）AS否

来自EMP

）

从CTE删除否则> 1

I have a table call EMP, which have 3 columns (EMP_NAME, EMP_MAIL, EMP_CONTACT) and does not have any primary key. Now i want to delete the duplicate row from this table using SQL inline query.

Below is the example.

emp_name emp_mail emp_contact

Arijita abcd@xyz 8122020706
Arijit abc@xy 8122020705
Arijit abc@xy 8122020705
Arijit abc@xy 8122020705

I search a query which will help me to delete the duplicate values.

Output should be :

emp_name || emp_mail || emp_contact

Arijita || abcd@xyz || 8122020706
Arijit || abc@xy || 8122020705

What I have tried:

I have found that how many duplicates are there.

解决方案

You can write a query for that something like following-

;WITH CTE(emp_name,emp_mail,emp_contact,SlNo)
AS
(
    SELECT emp_name,emp_mail,emp_contact,ROW_NUMBER() OVER(PARTITION BY emp_name,emp_mail,emp_contact ORDER BY emp_name,emp_mail,emp_contact) AS SlNo
    FROM Demo
)
DELETE FROM CTE WHERE SlNo > 1

For more details please visit my blog post at-
SQL Server: Delete/Update duplicate records | Technology Talks[^]

Hope, it helps :)

Take a look at these links
Removing Duplicates from a Table in SQL Server - Simple Talk
Delete duplicate rows in SQL Server

hope these links helps

;WITH CTE
AS
(
SELECT emp_name,emp_mail,emp_contact,ROW_NUMBER() OVER(PARTITION BY emp_name,emp_mail,emp_contact ORDER BY emp_name,emp_mail,emp_contact) AS No
FROM EMP
)
DELETE FROM CTE WHERE No > 1

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