SQL 删除几乎重复的行

2021-12-23 00:00:00 filter tsql sql-server-2008 sql-server duplicate-data

我有一个包含不幸数据的表，我正在尝试过滤掉一些数据.我确信 LName、FName 组合是唯一的，因为数据集小到可以验证.

I have a table that contains unfortuantely bad data and I'm trying to filter some out. I am sure that the LName, FName combonation is unique since the data set is small enough to verify.

LName, FName, Email
-----  -----  -----
Smith  Bob    bsmith@example.com
Smith  Bob    NULL
Doe    Jane   NULL
White  Don    dwhite@example.com

我希望查询结果带回没有NULL电子邮件的重复"记录，但在没有重复时仍然带回NULL电子邮件.

I would like to have the query results bring back the "duplicate" record that does not have a NULL email, yet still bring back a NULL Email when there is not a duplicate.

例如

Smith Bob   bsmith@example.com
Doe   Jane  NULL
White Don   dwhite@example.com

我认为解决方案类似于Sql，按值删除重复行，但我真的不明白提问者的要求是否和我的一样.

I think the solution is similar to Sql, remove duplicate rows by value, but I don't really understand if the asker's requirements are the same as mine.

有什么建议吗?

谢谢

推荐答案

如果有任何非空值，这将删除空行.

This drops the null rows if there are any non null values.

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', 'bsmith@example.com'
  UNION ALL SELECT 'Smith', 'Bob', 'NULL'
  UNION ALL SELECT 'Doe', 'Jane', 'NULL'
  UNION ALL SELECT 'White', 'Don', 'dwhite@example.com'

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

相关文章