Вот хороший способ дедупликации записей в таблице, в которой есть столбец идентификаторов, основанный на желаемом первичном ключе, который вы можете определить во время выполнения. Прежде чем я начну, я заполню образец набора данных для работы, используя следующий код:
if exists (select 1 from sys.all_objects where type='u' and name='_original')
drop table _original
declare @startyear int = 2017
declare @endyear int = 2018
declare @iterator int = 1
declare @income money = cast((SELECT round(RAND()*(5000-4990)+4990 , 2)) as money)
declare @salesrepid int = cast(floor(rand()*(9100-9000)+9000) as varchar(4))
create table #original (rowid int identity, monthyear varchar(max), salesrepid int, sale money)
while @iterator<=50000 begin
insert #original
select (Select cast(floor(rand()*(@endyear-@startyear)+@startyear) as varchar(4))+'-'+ cast(floor(rand()*(13-1)+1) as varchar(2)) ), @salesrepid , @income
set @salesrepid = cast(floor(rand()*(9100-9000)+9000) as varchar(4))
set @income = cast((SELECT round(RAND()*(5000-4990)+4990 , 2)) as money)
set @iterator=@iterator+1
end
update #original
set monthyear=replace(monthyear, '-', '-0') where len(monthyear)=6
select * into _original from #original
Далее я создам тип с именем ColumnNames:
create type ColumnNames AS table
(Columnnames varchar(max))
Наконец, я создам хранимую процедуру со следующими тремя оговорками: 1. Процедура будет принимать обязательный параметр @tablename, который определяет имя таблицы, которую вы удаляете из своей базы данных. 2. В процедуре есть необязательный параметр @columns, который вы можете использовать для определения полей, составляющих желаемый первичный ключ, который вы удаляете. Если это поле оставить пустым, предполагается, что все поля, кроме столбца идентификаторов, составляют желаемый первичный ключ. 3. При удалении повторяющихся записей будет сохранена запись с наименьшим значением в ее столбце идентификации.
Вот моя хранимая процедура delete_dupes:
create proc delete_dupes (@tablename varchar(max), @columns columnnames readonly)
as
begin
declare @table table (iterator int, name varchar(max), is_identity int)
declare @tablepartition table (idx int identity, type varchar(max), value varchar(max))
declare @partitionby varchar(max)
declare @iterator int= 1
if exists (select 1 from @columns) begin
declare @columns1 table (iterator int, columnnames varchar(max))
insert @columns1
select 1, columnnames from @columns
set @partitionby = (select distinct
substring((Select ', '+t1.columnnames
From @columns1 t1
Where T1.iterator = T2.iterator
ORDER BY T1.iterator
For XML PATH ('')),2, 1000) partition
From @columns1 T2 )
end
insert @table
select 1, a.name, is_identity from sys.all_columns a join sys.all_objects b on a.object_id=b.object_id
where b.name = @tablename
declare @identity varchar(max)= (select name from @table where is_identity=1)
while @iterator>=0 begin
insert @tablepartition
Select distinct case when @iterator=1 then 'order by' else 'over (partition by' end ,
substring((Select ', '+t1.name
From @table t1
Where T1.iterator = T2.iterator and is_identity=@iterator
ORDER BY T1.iterator
For XML PATH ('')),2, 5000) partition
From @table T2
set @iterator=@iterator-1
end
declare @originalpartition varchar(max)
if @partitionby is null begin
select @originalpartition = replace(b.value+','+a.type+a.value ,'over (partition by','') from @tablepartition a cross join @tablepartition b where a.idx=2 and b.idx=1
select @partitionby = a.type+a.value+' '+b.type+a.value+','+b.value+') rownum' from @tablepartition a cross join @tablepartition b where a.idx=2 and b.idx=1
end
else
begin
select @originalpartition=b.value +','+ @partitionby from @tablepartition a cross join @tablepartition b where a.idx=2 and b.idx=1
set @partitionby = (select 'OVER (partition by'+ @partitionby + ' ORDER BY'+ @partitionby + ','+b.value +') rownum'
from @tablepartition a cross join @tablepartition b where a.idx=2 and b.idx=1)
end
exec('select row_number() ' + @partitionby +', '+@originalpartition+' into ##temp from '+ @tablename+'')
exec(
'delete a from _original a
left join ##temp b on a.'+@identity+'=b.'+@identity+' and rownum=1
where b.rownum is null')
drop table ##temp
end
Как только это будет выполнено, вы можете удалить все свои повторяющиеся записи, запустив proc. Чтобы удалить дубликаты без определения желаемого первичного ключа, используйте этот вызов:
exec delete_dupes '_original'
Чтобы удалить дубликаты на основе определенного желаемого первичного ключа, используйте этот вызов:
declare @table1 as columnnames
insert @table1
values ('salesrepid'),('sale')
exec delete_dupes '_original' , @table1
person
Daniel Marcus
schedule
13.03.2018
empId
в вашем примере используется Джоном? - person armen   schedule 02.10.2013row_number() over (partition by EmployeeName order by EmployeeName)
... это выберет произвольную единственную запись для каждого имя. - person John Gibb   schedule 22.11.2013