删除空值较少的重复项

user3788671 发表于 Dev

用户3788671

我有一张雇员表，其中包含约25列。现在有很多重复项，我想尝试摆脱其中一些重复项。

首先，我想通过查找在名字，姓氏，员工编号，公司编号和状态中具有相同值的多个记录来查找重复项。

SELECT
    firstname,lastname,employeenumber, companynumber, statusflag
FROM
    employeemaster
GROUP BY
    firstname,lastname,employeenumber,companynumber, statusflag
HAVING 
    (COUNT(*) > 1)

这给了我重复的记录，但是我的目标是找到并保留最佳的单个记录并删除其他记录。“最佳单条记录”由在所有其他列中具有最少NULL值的记录定义。我怎样才能做到这一点？

我正在使用Microsoft SQL Server 2012 MGMT Studio。

例子：

在此处输入图片说明

红色：删除绿色：保持

注意：表中的列比该表显示的要多得多。

罗恩·史密斯

您可以使用sys.columns表获取列列表并构建动态查询。该查询将根据给定条件为您要保留的每条记录返回“ KeepThese”值。

-- insert test data
create table EmployeeMaster
  (
    Record int identity(1,1),
    FirstName varchar(50),
    LastName varchar(50),
    EmployeeNumber int,
    CompanyNumber int,
    StatusFlag int,
    UserName varchar(50),
    Branch varchar(50)
  );
insert into EmployeeMaster
  (
    FirstName,
    LastName,
    EmployeeNumber,
    CompanyNumber,
    StatusFlag,
    UserName,
    Branch
  )
  values
    ('Jake','Jones',1234,1,1,'JJONES','PHX'),
    ('Jake','Jones',1234,1,1,NULL,'PHX'),
    ('Jake','Jones',1234,1,1,NULL,NULL),
    ('Jane','Jones',5678,1,1,'JJONES2',NULL);

-- get records with most non-null values with dynamic sys.column query
declare @sql varchar(max)
select @sql = '
    select e.*,
        row_number() over(partition by
                            e.FirstName,
                            e.LastName,
                            e.EmployeeNumber,
                            e.CompanyNumber,
                            e.StatusFlag
                          order by n.NonNullCnt desc) as KeepThese
    from EmployeeMaster e
        cross apply (select count(n.value) as NonNullCnt from (select ' +
            replace((
                select 'cast(' + c.name + ' as varchar(50)) as value union all select '
                from sys.columns c
                where c.object_id = t.object_id
                for xml path('')
                ) + '#',' union all select #','') + ')n)n'
from sys.tables t
where t.name = 'EmployeeMaster'

exec(@sql)