Hello I have table name FriendsData that contains duplicate records as shown below
您好,我有表名FriendsData,包含重复记录,如下所示
fID UserID FriendsID IsSpecial CreatedBy
-----------------------------------------------------------------
1 10 11 FALSE 1
2 11 5 FALSE 1
3 10 11 FALSE 1
4 5 25 FALSE 1
5 10 11 FALSE 1
6 12 11 FALSE 1
7 11 5 FALSE 1
8 10 11 FALSE 1
9 12 11 FALSE 1
I want to remove duplicate combinations rows using MS SQL?
Remove latest duplicate records from MS SQL FriendsData table. here I attached image which highlights duplicate column combinations.
我想使用MS SQL删除重复的组合行?从MS SQL FriendsData表中删除最新的重复记录。这里我附加了突出重复列组合的图像。
How I can removed all duplicate combinations from SQL table?
我如何从SQL表中删除所有重复的组合?
4 个解决方案
#1
#2
3
It seems counter-intuitive, but you can delete from a common table expression (under certain circumstances). So, I'd do it like so:
这似乎违反直觉,但您可以从公用表表达式中删除(在某些情况下)。所以,我这样做:
with cte as (
select *,
row_number() over (partition by userid, friendsid order by fid) as [rn]
from FriendsData
)
delete cte where [rn] <> 1
This will keep the record with the lowest fid. If you want something else, change the order by clause in the over clause.
这将使记录保持最低的fid。如果您需要其他内容,请更改over子句中的order by子句。
If it's an option, put a uniqueness constraint on the table so you don't have to keep doing this. It doesn't help to bail out a boat if you still have a leak!
如果它是一个选项,请在表上放置唯一性约束,这样您就不必继续这样做了。如果你仍然有泄漏,那对救助船只没有帮助!
#3
1
I don't know if the syntax is correct for MS-SQL, but in MySQL, the query would look like:
我不知道MS-SQL的语法是否正确,但在MySQL中,查询看起来像:
DELETE FROM FriendsData WHERE fID
NOT IN ( SELECT fID FROM FriendsData
GROUP BY UserID, FriendsUserID, IsSpecial, CreatedBy)
In the GROUP BY clause you put the columns you need to be identical in order to consider two records duplicate
在GROUP BY子句中,您需要将相同的列放在一起,以便考虑两个记录重复
#4
0
Try this query,
试试这个查询,
select * from FriendsData f1, FriendsData f2
Where f1.fID=f2.fID and f1.UserID =f2.UserID and f1.FriendsID =f2.FriendsID
If it returns you the duplicate rows, then replace Select * by "Delete"
如果它返回重复的行,则将“删除”替换为“删除”
that will solve your problem
这将解决您的问题
#1
10
Try this
DELETE
FROM FriendsData
WHERE fID NOT IN
(
SELECT MIN(fID)
FROM FriendsData
GROUP BY UserID, FriendsID)
See here
Or here is more ways to do what you want
或者这里有更多方法可以做你想做的事
Hope this helps
希望这可以帮助
#2
3
It seems counter-intuitive, but you can delete from a common table expression (under certain circumstances). So, I'd do it like so:
这似乎违反直觉,但您可以从公用表表达式中删除(在某些情况下)。所以,我这样做:
with cte as (
select *,
row_number() over (partition by userid, friendsid order by fid) as [rn]
from FriendsData
)
delete cte where [rn] <> 1
This will keep the record with the lowest fid. If you want something else, change the order by clause in the over clause.
这将使记录保持最低的fid。如果您需要其他内容,请更改over子句中的order by子句。
If it's an option, put a uniqueness constraint on the table so you don't have to keep doing this. It doesn't help to bail out a boat if you still have a leak!
如果它是一个选项,请在表上放置唯一性约束,这样您就不必继续这样做了。如果你仍然有泄漏,那对救助船只没有帮助!
#3
1
I don't know if the syntax is correct for MS-SQL, but in MySQL, the query would look like:
我不知道MS-SQL的语法是否正确,但在MySQL中,查询看起来像:
DELETE FROM FriendsData WHERE fID
NOT IN ( SELECT fID FROM FriendsData
GROUP BY UserID, FriendsUserID, IsSpecial, CreatedBy)
In the GROUP BY clause you put the columns you need to be identical in order to consider two records duplicate
在GROUP BY子句中,您需要将相同的列放在一起,以便考虑两个记录重复
#4
0
Try this query,
试试这个查询,
select * from FriendsData f1, FriendsData f2
Where f1.fID=f2.fID and f1.UserID =f2.UserID and f1.FriendsID =f2.FriendsID
If it returns you the duplicate rows, then replace Select * by "Delete"
如果它返回重复的行,则将“删除”替换为“删除”
that will solve your problem
这将解决您的问题