lyfz_repos/经典版源码/document/数据库维护脚本/跨年转数据/client过滤掉完全重复和不完全重复记录 - 不包含2014.sql

--0.备份当前db的client表到client_back中;
select [id]
      ,[name1]
      ,[name2]
      ,[phone1]
      ,[phone2]
      ,[qq1]
      ,[qq2]
      ,[addr1]
      ,[addr2]
      ,[occupation1]
      ,[occupation2]
      ,[birthday1]
      ,[birthday2]
      ,[time3]
      ,[area]
      ,[area2]
      ,[check1]
      ,[check2]
      ,[check3]
      ,[sex]
      ,[zodiac]
      ,[photo] into [db].[dbo].[client_back] from [db].[dbo].[client]
go

--1.将db的client表全部记录插入到#tempall表中;
select [id]
      ,[name1]
      ,[name2]
      ,[phone1]
      ,[phone2]
      ,[qq1]
      ,[qq2]
      ,[addr1]
      ,[addr2]
      ,[occupation1]
      ,[occupation2]
      ,[birthday1]
      ,[birthday2]
      ,[time3]
      ,[area]
      ,[area2]
      ,[check1]
      ,[check2]
      ,[check3]
      ,[sex]
      ,[zodiac]
      ,[photo] into #tempall from [db].[dbo].[client]
go

--2.过滤掉完全重复的记录;
--select distinct * into #tempdis from #tempall
--go
--drop table #tempall
--go
--此步省略……

--3.获取指定字段的不重复记录,过滤掉不完全重复的记录;
select identity(int,1,1) as autoid, * into #temp1 from #tempall
go

select min(autoid) as autoid into #temp2 from #temp1 group by id
go

select [id]
      ,[name1]
      ,[name2]
      ,[phone1]
      ,[phone2]
      ,[qq1]
      ,[qq2]
      ,[addr1]
      ,[addr2]
      ,[occupation1]
      ,[occupation2]
      ,[birthday1]
      ,[birthday2]
      ,[time3]
      ,[area]
      ,[area2]
      ,[check1]
      ,[check2]
      ,[check3]
      ,[sex]
      ,[zodiac]
      ,[photo]
into [db].[dbo].[client#] from #temp1 where autoid in(select autoid from #temp2) order by id
go

--4.将过滤好的记录重新插入原表;
drop table #tempall
drop table #temp1
drop table #temp2
truncate table [db].[dbo].[client]
go

insert into [db].[dbo].[client]([id]
      ,[name1]
      ,[name2]
      ,[phone1]
      ,[phone2]
      ,[qq1]
      ,[qq2]
      ,[addr1]
      ,[addr2]
      ,[occupation1]
      ,[occupation2]
      ,[birthday1]
      ,[birthday2]
      ,[time3]
      ,[area]
      ,[area2]
      ,[check1]
      ,[check2]
      ,[check3]
      ,[sex]
      ,[zodiac]
      ,[photo]) 
select [id]
      ,[name1]
      ,[name2]
      ,[phone1]
      ,[phone2]
      ,[qq1]
      ,[qq2]
      ,[addr1]
      ,[addr2]
      ,[occupation1]
      ,[occupation2]
      ,[birthday1]
      ,[birthday2]
      ,[time3]
      ,[area]
      ,[area2]
      ,[check1]
      ,[check2]
      ,[check3]
      ,[sex]
      ,[zodiac]
      ,[photo] from [db].[dbo].[client#]
go

drop table [db].[dbo].[client#]
go
--5.结束;