包括
1、选择某些字段重复记录 保留除了最大的
2、选择某些字段重复记录
3、选择某些字段重复记录只保留一条最大的
--------自定义函数：分割字符串
if exists (select * from dbo.sysobjects where name='f_split' and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_split]
GO
create function f_split (@SourceSql varchar(400),@StrSeprate varchar(10))
returns @temp table(a varchar(100))--临时表
as
begin
declare @i int
set @SourceSql=rtrim(ltrim(@SourceSql))--去空格
set @i=charindex(@StrSeprate,@SourceSql)--返回@StrSeprate在@SourceSql中的位置
while @i>=1
begin
insert @temp values(left(@SourceSql,@i-1))
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)--返回剩余字符串
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql<>'\'--以'\'结束并忽略此字符
insert @temp values(@SourceSql)
return
end
go
----------
--选择某些字段重复记录 保留除了最大的
--@table表名称
--@fieldlist字段列表
--@serialfd 排序列
if object_id('repeatfield','p') is not null drop procedure repeatfield
go
create proc repeatfield (@table varchar(100) ,@fieldlist varchar(1000) ,@serialfd varchar(100))
as begin
declare @sqlstr1 varchar(8000)
declare @sqlstr varchar(8000)
declare @sqlstr2 varchar(8000)
--选择某些字段重复记录
set @sqlstr='select * from '+@table
set @sqlstr=@sqlstr+'
where 1=1 '
select @sqlstr=@sqlstr+'
and
'+a+'
in (
select '+a+' from
(
select '+@fieldlist+' from '+@table
+'
where '+a+'<>'+''''+''+''''+' and '+a+' is not null '
+'
group by '+@fieldlist
+'
having count(*)>1 '
+'
) t
)'
from dbo.f_split(@fieldlist,',')
set @sqlstr=' select * from
(
'+ @sqlstr+ '
)tt
where '+@serialfd+' not in( '
--选择某些字段重复记录只保留一条最大的
set @sqlstr1='select * from '+@table
set @sqlstr1=@sqlstr1+'
where 1=1 '
select @sqlstr1=@sqlstr1+'
and
'+a+'
in (
select '+a+' from
(
select '+@fieldlist+' from '+@table
+'
where '+a+'<>'+''''+''+''''+' and '+a+' is not null '
+'
group by '+@fieldlist
+'
having count(*)>1 '
+'
) t
)'
from dbo.f_split(@fieldlist,',')
set @sqlstr1=' select max( '+@serialfd+' ) as '+@serialfd+' , fcity_distruct,fpiece,ffloorplate,fridgepole,fcell,fhousenumber from
( '
+@sqlstr1
+' )tt
group by fcity_distruct,fpiece,ffloorplate,fridgepole,fcell,fhousenumber '
set @sqlstr1=' select '+@serialfd+'
from
(
select * from
('+@sqlstr1+'
)ttt
)tttt
) '
set @sqlstr2=@sqlstr+@sqlstr1
exec (@sqlstr2)
end
go
repeatfield 'houseresouceinformation', 'fcity_distruct,fpiece,ffloorplate,fridgepole,fcell,fhousenumber','fzlbh'
----------
--选择某些字段重复记录
--@table表名称
--@fieldlist字段列表
--@wherestr条件
if object_id('repeatfield','p') is not null drop procedure repeatfield
go
create proc repeatfield (@table varchar(100) ,@fieldlist varchar(1000) ,@wherestr varchar(1000))
as begin
declare @sqlstr varchar(4000)
set @sqlstr='select * from '+@table
set @sqlstr=@sqlstr+'
where 1=1 '
select @sqlstr=@sqlstr+'
and
'+a+'
in (
select '+a+' from
(
select '+@fieldlist+' from '+@table
+'
where '+a+'<>'+''''+''+''''+' and '+a+' is not null '
+ @wherestr
+'
group by '+@fieldlist
+'
having count(*)>1 '
+'
) t
)'
from dbo.f_split(@fieldlist,',')
exec ( @sqlstr)
end
go
repeatfield 'houseresouceinformation', 'fcity_distruct,fpiece,ffloorplate,fridgepole,fcell,fhousenumber' ,' and omit=0'
----------
--选择某些字段重复记录只保留一条最大的
--@table表名称
--@fieldlist字段列表
--@serialfd 排序列
if object_id('repeatfield','p') is not null drop procedure repeatfield
go
create proc repeatfield (@table varchar(100) ,@fieldlist varchar(1000),@serialfd varchar(100) )
as begin
declare @sqlstr varchar(4000)
set @sqlstr='select * from '+@table
set @sqlstr=@sqlstr+'
where 1=1 '
select @sqlstr=@sqlstr+'
and
'+a+'
in (
select '+a+' from
(
select '+@fieldlist+' from '+@table
+'
where '+a+'<>'+''''+''+''''+' and '+a+' is not null '
+'
group by '+@fieldlist
+'
having count(*)>1 '
+'
) t
)'
from dbo.f_split(@fieldlist,',')
set @sqlstr=' select max( '+@serialfd+' ) as '+@serialfd+' , fcity_distruct,fpiece,ffloorplate,fridgepole,fcell,fhousenumber from
( '
+@sqlstr
+' )tt
group by fcity_distruct,fpiece,ffloorplate,fridgepole,fcell,fhousenumber '
exec( @sqlstr)
end
go
repeatfield 'houseresouceinformation', 'fcity_distruct,fpiece,ffloorplate,fridgepole,fcell,fhousenumber' ,'fzlbh' |
