多条重复数据取第一条-CSDN博客

使用分析函数row_number() over (partiion by ... order by ...)来进行分组编号，然后取分组标号值为1的记录即可。目前主流的数据库都有支持分析函数，很好用。

其中，partition by 是指定按哪些字段进行分组，这些字段值相同的记录将在一起编号；order by则是指定在同一组中进行编号时是按照怎样的顺序。

示例(SQL Server 2005或以上适用)：

select s.*
from (
    select *, row_number() over (partition by [手机号] order by [店铺]) as group_idx
    from table_name
) s
where s.group_idx = 1 --------------------- 本文来自 KamChau 的CSDN 博客，全文地址请点击：https://blog.csdn.net/weixin_40439880/article/details/78190427?utm_source=copy

SqlServer多条重复数据删除：

#####################################

--取到重复数据，放到一个表中
select *
into temp1
from IM_ICD10_YM
where YM_Code in (
select YM_Code
from IM_ICD10_YM
group by YM_Code
having count(*) > 1)

--删掉原表中的所有重复数据
delete from IM_ICD10_YM
where YM_Code in (
select YM_Code
from temp1
)

--把重复数据排序，取第一条放回原表
insert into IM_ICD10_YM(YM_Code,YM_Name)
select s.YM_Code,s.YM_Name
from (
select *, row_number() over (partition by YM_Code order by YM_name) as group_idx
from temp1
) s
where s.group_idx = 1

#########################################

1.查询出来重复数据, 放进一个新表里:

select id,name

from( select id,name, row_number() over (partition by [id] order by [任意字段]) as group_idx from table ) a

where a.group_idx >1

into new_table(id,name)

2.删掉旧表中的所有重复数据：

delete from table

where id in (select distinct id from new table)

3.把新表里边的重复数据 distinct 一下放回旧表里：

insert into table（ID，name）

select distinct * from new_table