sql – 删除空值较小的重复项

我有一张员工表,其中包含大约25列.现在有很多重复,我想尝试摆脱一些重复.

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

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

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

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

例：

红色：删除
绿色：保持

注意：表中列的列数多于此表所示的列数.

解决方法

-- 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,'JJONES','PHX'),('Jake',NULL,NULL),('Jane',5678,'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)

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!