删除表中的重复项 [英] Remove dups in table

问题描述

我以为我有这个，但事实证明它正在丢弃一些它不应该。



我正在将数据从旧表迁移到新表中表格不同，所以我必须要有点创意，因为我是SQL文盲。



表定义为：

I thought I had this but turns out it's dropping some it shouldn't.

I'm migrating data from an old table into a new table and the tables are dissimilar so am having to be a little creative since I'm SQL illiterate.

The table is defined as;

public partial class User
{
    public int UserID { get; set; }
    public string Name { get; set; }
    public string ImageFile { get; set; }
    public string Password { get; set; }
    public string Email { get; set; }
    public string WebSite { get; set; }
    public string DisplayName { get; set; }
    public bool CanComment { get; set; }
    public bool IsActive { get; set; }
    public System.DateTime CreateDate { get; set; }
    public string Custom { get; set; }
}

而我正在尝试的是使用电子邮件删除所有重复的用户。这是我认为我曾经工作过的查询，但似乎没有做我想要的事情？

And what I'm trying to do is delete all duplicate users using email. This is the query that I thought I had working but doesn't seem to be doing what I want?

delete from [User] where Email in
(select email from [User] group by Email having Count(Email) > 1)

感谢您给予的任何帮助

Thanks for any help given

推荐答案

我建​​议编写如下查询：

I'd suggest to write query as follow:

SELECT u.*, ROW_NUMBER() OVER(ORDER BY Email, UserID) AS RowNo
FROM [User] as u

这应生成带有RowNumbers的记录集。

要删除重复项，请使用：

This should produce a recordset with RowNumbers.
To delete duplicates, use this:

DELETE
FROM (
    SELECT u.*, ROW_NUMBER() OVER(ORDER BY Email, UserID) AS RowNo
    FROM [User] as u
) AS t1
WHERE t1.RowNo>1

另一种方式是使用：

Another way is to use:

DELETE
FROM (
    SELECT u.*, ROW_NUMBER() OVER(PARTITION BY Email ORDER BY UserID) AS RowNo
    FROM [User] as u
) AS t1
WHERE t1.RowNo>1

只是再给你一个选项，如果UserId是唯一的那么只需

Just to throw you one more option, if UserId is unique then simply

DELETE FROM [User] a
WHERE EXISTS (SELECT 1
              FROM [User] b
              WHERE b.email = a.email
              AND   b.userid > a.userid)

或者如果没有密钥，你可以使用%% physloc %%。有关详细信息，请参阅如何删除SQL中的重复行没有密钥时的Server 2008 [ ^ ]

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