--建表 create table #test(instrumentID int, re varchar(20)) insert into #test(instrumentID,re) select 1,null union all select 2,null union all select 2,'value' union all select 3, null union all select 3, null union all select 3, 'value' --删除语句 with cte as( select instrumentID,re,ROW_NUMBER ()over(partition by re order by instrumentID) as row from #test) delete from cte where row>1 and re is nullselect * from #test --查询结果 1 NULL 2 value 3 value
--建表
create table #test(instrumentID int, re varchar(20))
insert into #test(instrumentID,re)
select 1,null union all
select 2,null union all
select 2,'value' union all
select 3, null union all
select 3, null union all
select 3, 'value' --删除语句
with cte as(
select instrumentID,re,ROW_NUMBER ()over(partition by re order by instrumentID) as row from #test)
delete from cte where row>1 and re is nullselect * from #test
--查询结果
1 NULL
2 value
3 value