--------自定义函数:分割字符串 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 ) '
---------- --选择某些字段重复记录 --@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 '
--修改下 --------创建表table cc if exists (select name from sysobjects where name='cc' and xtype='u') drop table cc go
create table cc (a varchar(10) ,b varchar(10) ,c varchar(10) ,d int ,id int IDENTITY (1, 1) NOT NULL primary key(id)) go
insert cc select 'a','b' ,'d',1 union all select 'a','b' ,'d',1 union all select 'a','b' ,'d',1 union all select 'a','b' ,'r',1 union all select 'a','b' ,'r',1 union all select 'a','b' ,'d',2 union all select 'a','b' ,'d',2 go
--select * from cc
--------自定义函数:分割字符串 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
----------选择某些字段重复记录procedure --@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',--@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 ' + @wherestr
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,',') --print @sqlstr exec ( @sqlstr) end go ---------- -- -----实例 -- repeatfield 'cc', 'a,b' , ' and c=''d'' ' -- --结果 -- a b c d id -- ---------- ---------- ---------- ---------- ---------- -- a b d 1 1 -- a b d 1 2 -- a b d 1 3 -- a b d 2 6 -- a b d 2 7 -- -- (所影响的行数为 5 行)
----------选择某些字段重复记录只保留一条最大的repeatfieldmax if object_id('repeatfieldmax','p') is not null drop procedure repeatfieldmax go
create proc repeatfieldmax (@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+' , '+@fieldlist+' from ( ' +@sqlstr +' )tt group by '+@fieldlist
-- print @sqlstr exec( @sqlstr) end go ---------- ---------- -- -----实例 -- repeatfieldmax 'cc', 'a,b,c' ,'id' -- --结果 -- id a b c -- ---------- ---------- ---------- ---------- -- 7 a b d -- 5 a b r -- -- (所影响的行数为 2 行) ----------选择某些字段重复记录 保留除了最大的repeatfieldexceptmax --@table表名称 --@fieldlist字段列表 --@serialfd 排序列 if object_id('repeatfieldexceptmax','p') is not null drop procedure repeatfieldexceptmax go
create proc repeatfieldexceptmax (@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+' , '+@fieldlist+' from ( ' +@sqlstr1 +' )tt group by '+@fieldlist
set @sqlstr1=' select '+@serialfd+' from ( select * from ('+@sqlstr1+' )ttt )tttt ) '
set @sqlstr2=@sqlstr+@sqlstr1 exec (@sqlstr2)
end go
---------- -- -----实例 -- repeatfieldexceptmax 'cc', 'a,b,c' ,'id' -- --结果 -- a b c d id -- ---------- ---------- ---------- ---------- ---------- -- a b d 1 1 -- a b d 1 2 -- a b d 1 3 -- a b r 1 4 -- a b d 2 6 -- -- (所影响的行数为 5 行)
--修改 --------创建表table cc if exists (select name from sysobjects where name='cc' and xtype='u') drop table cc go
create table cc (a varchar(10) ,b varchar(10) ,c varchar(10) ,d int ,id int IDENTITY (1, 1) NOT NULL primary key(id)) go
insert cc select 'a','b' ,'d',1 union all select 'a','b' ,'d',1 union all select 'a','b' ,'d',1 union all select 'a','b' ,'r',1 union all select 'a','b' ,'r',1 union all select 'a','b' ,'d',2 union all select 'a','b' ,'d',2 go
--select * from cc
--------自定义函数:分割字符串 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
----------选择某些字段重复记录procedure --@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 ' + @wherestr
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,',') --print @sqlstr exec ( @sqlstr) end go ---------- -- -----实例 -- repeatfield 'cc', 'a,b' , ' and c=''d'' ' -- --结果 -- a b c d id -- ---------- ---------- ---------- ---------- ---------- -- a b d 1 1 -- a b d 1 2 -- a b d 1 3 -- a b d 2 6 -- a b d 2 7 -- -- (所影响的行数为 5 行)
----------选择某些字段重复记录只保留一条最大的repeatfieldmax if object_id('repeatfieldmax','p') is not null drop procedure repeatfieldmax go
create proc repeatfieldmax (@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+' , '+@fieldlist+' from ( ' +@sqlstr +' )tt group by '+@fieldlist
-- print @sqlstr exec( @sqlstr) end go ---------- ---------- -- -----实例 -- repeatfieldmax 'cc', 'a,b,c' ,'id' -- --结果 -- id a b c -- ---------- ---------- ---------- ---------- -- 7 a b d -- 5 a b r -- -- (所影响的行数为 2 行) ----------选择某些字段重复记录 保留除了最大的repeatfieldexceptmax --@table表名称 --@fieldlist字段列表 --@serialfd 排序列 if object_id('repeatfieldexceptmax','p') is not null drop procedure repeatfieldexceptmax go
create proc repeatfieldexceptmax (@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+' , '+@fieldlist+' from ( ' +@sqlstr1 +' )tt group by '+@fieldlist
set @sqlstr1=' select '+@serialfd+' from ( select * from ('+@sqlstr1+' )ttt )tttt ) '
set @sqlstr2=@sqlstr+@sqlstr1 exec (@sqlstr2)
end go
---------- -- -----实例 -- repeatfieldexceptmax 'cc', 'a,b,c' ,'id' -- --结果 -- a b c d id -- ---------- ---------- ---------- ---------- ---------- -- a b d 1 1 -- a b d 1 2 -- a b d 1 3 -- a b r 1 4 -- a b d 2 6 -- -- (所影响的行数为 5 行)