SQL查询避免从数据库中重复数据 [英] Sql query for avoiding repeatative data from data base

问题描述

朋友们，
不幸的是，在我的数据库中，字段增加了一倍.
例如:在数据库中，我想要原始的一个数据，例如"Enquiryid = 123和Coursename = nnn"
但是现在在我的数据库中，所有数据都翻了一倍；
例如:查询编号= 123，密码名称= nnn"
"Enquiryid = 123和Courcename = nnn"
我想从数据库中删除重复的"enquiry_id"，但应保留重复的"enquiry_id"及其对应行的原始.......
你得到我吗??????然后给我这个问题的SQL查询

Hi friends,
In my database unfortunately doubled one field.
Eg: In database ,I want original one data like "Enquiryid=123 and Coursename=nnn"
But now in my database every data is doubled;
Eg: "Enquiryid=123 and Courcename=nnn"
"Enquiryid=123 and Courcename=nnn"
I want to delete repeated ''enquiry_id''from my database but there should remaine original of the repeated ''enquiry_id'' and its corresponding rows.......
do u get me????????then give me the sql query for this problem

推荐答案

嗨.
保留(删除)重复项:

SELECT Enquiryid，课程名称，ROW_NUMBER()OVER(ORDER BY SlNo)AS``Row Number''，*
来自员工
GROUP BY Enquiryid，课程名称
拥有Count(*)> 1


要删除重复项:

; with TBLCTE(Enquiryid，Coursename)
AS
(
选择
询问编号，课程名称，
排名= DENSE_RANK()超过(PARTITION BY Enquiryid，课程名称顺序由newID())
来自员工
)
从TBLCTE删除，其中排名> 1

从员工中选择*

Hi.. Try below

To retain (eleminate) duplicate:

SELECT Enquiryid,Coursename,ROW_NUMBER() OVER(ORDER BY SlNo ) AS ''Row Number'',*
FROM employee
GROUP BY Enquiryid,Coursename
Having Count(*)>1


To Delete duplicate:

;with TBLCTE(Enquiryid,Coursename)
AS
(
select
Enquiryid,Coursename,
Ranking = DENSE_RANK() over (PARTITION BY Enquiryid,Coursename order by newID())
from Employee
)
delete from TBLCTE where Ranking > 1

select * from Employee

尝试一下.

Try this.

DELETE TOP(1) FROM tablename WHERE Enquiryid = 123 AND Coursename = 'nnn'"

查看以下链接 MSDN:如何从SQL Server中的表中删除重复的行

这篇关于SQL查询避免从数据库中重复数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！