SQL 按照多个字段为标准的重复数据处理

表结构：T1

id int Unchecked
name nchar(10) Checked
value nchar(10) Checked
 

数据：

1 a a1
2 a a1
3 b b1
4 b b2
5 c c1
6 c c1
7 c c2

 

以name和value为标准

所有数据查询，重复行只保留ID最小的一条：

select id,name,value from
(select no=row_number() over(partition by name,value order by id),* from T_Test) t
where no=1

结果：

1 a a1
3 b b1
4 b b2
5 c c1
7 c c2

 

删除重复行，保留重复行ID最小的一条：

DELETE FROM T_Test where id in(

select id from
(select no=row_number() over(partition by name,value order by id),* from T_Test) t
where no>1

)

结果：ID为2和6的行被删除。

 

查询重复数据，保留ID最小项：

SELECT * FROM T_Test t1
WHERE t1.name IN (SELECT NAME FROM T_Test GROUP BY NAME,value HAVING COUNT(*) >1)
and t1.id in(
select min(id) FROM T_Test GROUP BY NAME,value HAVING COUNT(*) >1)

结果：

1 a a1
5 c c1

 

转载于:https://www.cnblogs.com/Andy-Li/p/6556390.html