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

use db
--0.备份当前db的dindanjd表到dindanjd_back中;
select * into [db].[dbo].[dindanjd_back] from [db].[dbo].[dindanjd]
go

--1.将db的dindanjd表全部记录插入到#tempall表中;
select * into #tempall from [db].[dbo].[dindanjd]
go

--2.过滤掉完全重复的记录;
select distinct * into #tempdis from #tempall
go

drop table #tempall
go

--3.获取指定字段的不重复记录,过滤掉不完全重复的记录;
select identity(int,1,1) as autoid, * into #temp1 from #tempdis
select min(autoid) as autoid into #temp2 from #temp1 group by id,name
select [id],[name],[date],[time],[waiter1],[waiter2],[status],[waiter12],[waiter22],[bookingdate],[dress],[bz],[clerk],[inputtime],[branch]
into [db].[dbo].[dindanjd#] from #temp1 where autoid in(select autoid from #temp2) order by id
go

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

insert into [db].[dbo].[dindanjd] select * from [db].[dbo].[dindanjd#]
go

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