我有一张员工表,其中包含大约25列.现在有很多重复,我想尝试摆脱一些重复.
首先,我想通过查找在名字,姓氏,员工编号,公司编号和状态中具有相同值的多个记录来查找重复项.
SELECT
firstname,lastname,employeenumber,companynumber,statusflag
FROM
employeemaster
GROUP BY
firstname,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,'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)
