instrumentID remark
1 null
2 value
2 null
因为一个instrumentID可能对应2条记录,即 remark有值或 remark为null,
怎么治去除2条记录中remark为null的记录
但又不影响instrumentID只对应1条记录的的情况
即上面示例:只去除第3条记录,不影响第一条记录
------解决方案--------------------
select instrumentID,max(remark)
from 表名
group by instrumentID
------解决方案--------------------
--建表
create table #test(instrumentID int, remark varchar(20))
insert into #test(instrumentID,remark)
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,remark,ROW_NUMBER ()over(partition by remark order by instrumentID) as row from #test)
delete from cte where row>1 and remark is null
select * from #test
--查询结果
1 NULL
2 value
3 value