SQL删除几乎重复的行

Question

我有一张表格，里面的数据不太好，我正在尝试筛选一些出来。由于数据集很小，我确信LName、FName组合是唯一的。

LName, FName, Email
-----  -----  -----
Smith  Bob    [e-mail]
Smith  Bob    NULL
Doe    Jane   NULL
White  Don    [e-mail]

我希望查询结果能够返回“重复”的记录，但不包括Email为空的记录，当没有重复时仍需返回Email为空的记录。

例如：

Smith Bob   [e-mail]
Doe   Jane  NULL
White Don   [e-mail]

我认为解决方案类似于Sql, 按值删除重复行，但我不确定提问者的要求是否与我的相同。

有什么建议吗？

谢谢

- jmmr

---

感谢Cybernate和Michael Goldshteyn提供的解决方案。选择FooLman是因为它是第一个完成任务的解决方案。看到不同的解决方法很有趣。 - jmmr

Answer 1

您可以使用ROW_NUMBER()分析函数:

SELECT *
  FROM (
                SELECT a.*, ROW_NUMBER() OVER(PARTITION BY LName, FName ORDER BY Email DESC) rnk
                    FROM <YOUR_TABLE> a
                ) a
WHERE RNK = 1

- Chandu

---

+1 这种方法可能比自连接更有效。根据我的回答，这种技术可以用于删除。 - Martin Smith

Answer 2

如果存在非空值，则删除空行。

SELECT  lname
        , fname
        , MIN(email)
FROM    YourTable
GROUP BY
        lname
        , fname

测试脚本

DECLARE @Test TABLE (
  LName VARCHAR(32)
  , FName VARCHAR(32)
  , Email VARCHAR(32)
)

INSERT INTO @Test
  SELECT 'Smith', 'Bob', '[e-mail]'
  UNION ALL SELECT 'Smith', 'Bob', 'NULL'
  UNION ALL SELECT 'Doe', 'Jane', 'NULL'
  UNION ALL SELECT 'White', 'Don', '[e-mail]'

SELECT  lname
        , fname
        , MIN(Email)        
FROM    @Test
GROUP BY
        lname
        , fname

- FooLman

---

+1 这绝对是发布的最简单的解决方案，可以完成所需的任务。社区似乎遭受了过度工程化的困扰<g>。 - Lieven Keersmaekers

@Lieven - 不过这样行不通！如果没有非空行，保留空行有什么逻辑呢？ - Martin Smith

@Martin，这个查询根据给定的输入产生了op所请求的输出，是吗（还是我又搞砸了）？ - Lieven Keersmaekers

@Lieven - 啊，我明白了，这是提供要保留的记录而不是要删除的记录。抱歉！请继续！ - Martin Smith

抱歉标题可能有些混淆，我想要从查询结果中“删除”空行。 - jmmr

Answer 3

这里有一个相对简单的查询，使用标准SQL实现，并且只做了这个：

SELECT * FROM Person P
WHERE Email IS NOT NULL OR -- Take all people with non-null e-mails
      Email IS NULL AND    -- and all people with null e-mails, as long as
        NOT EXISTS         -- there is no duplicate record of the same person
          (SELECT *        -- with a non-null e-mail
           FROM Person P2 
           WHERE P2.LName=P.LName AND P2.FName=P.FName AND P2.Email IS NOT NULL)

- Michael Goldshteyn

---

你是在暗示 row_number 不是标准的 SQL 吗？ - Martin Smith

我并不意味着任何事情 - 只是提供一个简单的解决方案，仅使用涉及子查询的标准SQL查询。但是，如果您想要关于ROW_NUMBER的问题的答案，那么它不是标准SQL，PARTITION BY也不是。 - Michael Goldshteyn

根据此处所述，ANSI SQL 1999并非由OP指定的标准SQL。 - Martin Smith

Answer 4

由于已经有很多SQL解决方案发布了，您可能想要创建一个数据修复程序来删除不良数据，然后添加必要的约束条件以防止不良数据被插入。数据库中的不良数据是设计不良的副作用。

- Billworth Vandory

---

我同意并理解，但是在企业IT领域中，我无能为力。这就是我必须处理的数据的现实。 - jmmr

@jrm82，对于企业应用程序来说，修复这样的问题更加重要！仅仅因为它是一个API并不意味着你不应该这样做。如果你不修复，这将会永远引起无尽的问题。 - HLGEM

HLGEM - 我不“拥有”数据，也没有能力处理任何我想要的东西。 - jmmr