如何从表中删除重复数据? [英] 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屋!