sql-server - 停用基于两个分组列的重复数据，检查不为空

Question

我有以下查询以 2 列返回重复行，但它返回具有 NULL 值的行。我想为两列返回 NOT NULL 的行。另外，如何停用旧 ID 的重复记录？

With deactivaterows as (

  Select t.*, count(*) over (partition by col1, col2) as cnt from TABLE1 t)

Select * from deactivaterows where cnt > 1;

标签： sql-servertsqlsql-server-2016

Answer 1

您希望 row_number 不计数（*）：

with deactivaterows as
(
select t.*, row_number() over(partition by t.col1, t.col2 order by (select null)) as rn
from TABLE1
where t.col1 is not null and t.col2 is not null
)
select * from deactivaterows where rn > 1