软件:sql server 2014
核心语句:
select row_number() over (
partition by 重复字段 order by 排序字段
) as flag, * from 表名
1 准备语句:
drop table eDelRepeatData
create table eDelRepeatData
(
ID int identity(1,1) ,
aaa int,
bbb int,
ccc int
)
Insert Into eDelRepeatData(aaa,bbb,ccc)
Select 1,2,3
union all
Select 1,2,3
union all
Select 1,2,3
union all
Select 3,2,1
union all
Select 3,2,1
union all
Select 4,5,6
测试下:select * from eDelRepeatData
由结果见,字段aaa,bbb,ccc三个字段都重复的数据有ID为1,2,3这三条重复,4,5重复;
2 接下来就是对数据进行分组:
select row_number() over (
partition by aaa, bbb, ccc order by id
) as flag, * from